在Apex把csv导入数据库Clob字段再导入到各自对应列的解决方法

CREATE TABLE "TABLE3"

( "CONTENT" CLOB

) ;

CREATE TABLE "TABLE4"

( "NAME" VARCHAR2(20 BYTE),

"SID" VARCHAR2(20 BYTE)

) ;

a11,1

b2,2

c33,3

create or replace directory csv as '/home/oracle/csv' ;

grant read,write on directory csv to user1;

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;

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

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