Oracle Apex 实用笔记系列 5 - 在Apex把csv导入数据库Clob字段再导入到各自对应列的解决方法

1. 需求

有一用户数据存在于csv文件，因为Apex不允许上传超过44列的数据（在该案例中有90多列），所以需求是把所有列先导入到一个clob字段，然后再用存储过程导出到对应的列。

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_max_line_length integer := 32767;
l_buffer varchar2(32767);
l_file UTL_FILE.FILE_TYPE; 
l_clob clob;
BEGIN

l_file := utl_file.fopen('CSV', 'book1.csv', 'r', l_max_line_length);

dbms_lob.createtemporary(l_clob, TRUE, DBMS_LOB.session);

loop
begin
utl_file.get_line(l_file, l_buffer);

dbms_lob.append(l_clob, l_buffer||';');
exception
when no_data_found then
exit;
end;
end loop;

insert into table3 (content) values (l_clob);

dbms_lob.freetemporary(l_clob);

UTL_FILE.FCLOSE(l_file);

END writecsvintoclob;

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. 注意事项

由于有90多列，可能字符串的长度会超过32767，这需要额外处理。