在Apex把csv导入数据库Clob字段再导入到各自对应列的解决方法
1. 需求
2.解决方法
1) 创建一个有clob字段的表
CREATE TABLE "TABLE3" ( "CONTENT" CLOB ) ; |
2)创建一个具有真实列的表
CREATE TABLE "TABLE4" ( "NAME" VARCHAR2(20 BYTE), "SID" VARCHAR2(20 BYTE) ) ; |
3) 准备一个csv文件
比如,文件名叫book1.csv,文件格式如下
a11,1 b2,2 c33,3 |
4) 把csv放到一个目录下
比如/home/oracle/csv
同时,在oracle建立一个directory对象
create or replace directory csv as ‘/home/oracle/csv‘ ; grant read,write on directory csv to user1; |
5) 写一个存储过程把csv放入clob
create or replace PROCEDURE writecsvintoclob AS l_file := utl_file.fopen(‘CSV‘, ‘book1.csv‘, ‘r‘, l_max_line_length); dbms_lob.createtemporary(l_clob, TRUE, DBMS_LOB.session);
loop dbms_lob.freetemporary(l_clob); UTL_FILE.FCLOSE(l_file);
|
6) 写一个子存储过程把varchar放入列 (为把clob放入列做准备)
CREATE OR REPLACE PROCEDURE PUTVARCHARINTOCOL ( P_BUFFER IN VARCHAR2 ) AS l_len number; l_start number := 1; l_end number := 32767; l_amount number:=32767; l_field varchar2(32767); l_buffer varchar2(32767); i number :=1; l_sql varchar2(32767); BEGIN l_buffer := p_buffer || ‘,‘; l_len :=length(l_buffer); --dbms_output.put_line(‘l_len=‘||l_len); l_end := instr(l_buffer, ‘,‘, l_start); l_sql := ‘insert into table4 (name,sid) values (‘; while(l_start<l_len) loop -- dbms_output.put_line(‘l_start=‘||l_start||‘,l_end=‘||l_end); l_amount := (l_end-l_start); --dbms_output.put_line(‘l_amount=‘||l_amount); dbms_lob.read(l_buffer, l_amount, l_start, l_field); dbms_output.put_line(‘field #‘||i||‘:‘||l_field); l_sql := l_sql || ‘‘‘‘||l_field||‘‘‘,‘; i :=i+1; l_start := l_end+1; l_end := instr(l_buffer, ‘,‘, l_start); end loop; l_sql := substr(l_sql,1,length(l_sql)-1); l_sql := l_sql || ‘)‘; dbms_output.put_line(‘l_sql=‘||l_sql); EXECUTE IMMEDIATE l_sql; END PUTVARCHARINTOCOL; |
7) 写一个存储过程把clob放入列
create or replace PROCEDURE putCLOBINTOcol AS l_clob clob; l_start number := 1; l_end number := 32767; l_amount number:=32767; l_buffer varchar2(32767); l_len number; i number:=1; BEGIN select content into l_clob from table3; l_len := dbms_lob.getlength(l_clob); --dbms_output.put_line(‘l_len=‘||l_len); l_end := instr(l_clob, ‘;‘, l_start); while(l_start<l_len) loop --dbms_output.put_line(‘l_start=‘||l_start||‘,l_end=‘||l_end); l_amount := (l_end-l_start); --dbms_output.put_line(‘l_amount=‘||l_amount); dbms_lob.read(l_clob, l_amount, l_start, l_buffer); dbms_output.put_line(‘Line #‘||i||‘:‘||l_buffer); PUTVARCHARINTOCOL(l_buffer); i :=i+1; l_start := l_end+1; l_end := instr(l_clob, ‘;‘, l_start); end loop; END putCLOBINTOcol; |
3. 注意事项
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。