在Apex把csv匯入資料庫Clob欄位再匯入到各自對應列的解決方案,apexclob
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,檔案格式如下
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,這需要額外處理。