How to import the csv data to the Clob field of the database in Apex and then to the corresponding column? apexclob

How to import the csv data to the Clob field of the database in Apex and then to the corresponding column? apexclob
1. the user data needs to exist in the csv file. Because Apex does not allow uploading of more than 44 columns of data (more than 90 columns in this case), you must first import all columns to a clob field, then, use the stored procedure to export the data to the corresponding column. 2. Solution

1) create a table with a clob Field

Create table "TABLE3" ("CONTENT" CLOB );

2) create a table with real columns

Create table "TABLE4" ("NAME" VARCHAR2 (20 BYTE ), "SID" VARCHAR2 (20 BYTE) );

3) Prepare a csv file

For example, the file name is book1.csv. The file format is as follows:

A11, 1 B2, 2 C33, 3

4) Put the csv file in a directory.

For example,/home/oracle/csv

Create a directory object in oracle

Create or replace directory csv as '/home/oracle/csv '; Grant read, write on directory csv to user1;

5) Write a stored procedure and put csv into clob.

Create or replace PROCEDURE writecsv1_clob Rochelle max_line_length integer: = 32767; Rochelle buffer varchar2 (32767 ); L_file UTL_FILE.FILE_TYPE; Rochelle 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 writecsv1_clob;

6) Write a sub-storage process to put varchar into the column (to prepare for putting clob into the column)

CREATE OR REPLACE PROCEDURE PUTVARCHARINTOCOL ( P_BUFFER IN VARCHAR2 ) Rochelle Len number; Rochelle start number: = 1; L_end number: = 32767; Rochelle amount number: = 32767; Rochelle field varchar2 (32767 ); Rochelle buffer varchar2 (32767 ); I number: = 1; L_ SQL varchar2 (32767 ); BEGIN L_buffer: = p_buffer | ','; Rochelle Len: = length (l_buffer ); -- Dbms_output.put_line ('L _ len = '| l_len ); Rochelle end: = instr (l_buffer, ',', Rochelle 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 ); Rochelle 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 ); Rochelle SQL: = Rochelle SQL | ''' | l_field | ''','; I: = I + 1; Rochelle start: = Rochelle end + 1; Rochelle end: = instr (l_buffer, ',', Rochelle start ); End loop; L_ SQL: = substr (l_ SQL, 1, length (l_ SQL)-1 ); Rochelle SQL: = Rochelle SQL | ')'; Dbms_output.put_line ('L _ SQL = '| l_ SQL ); Execute immediate l_ SQL; End putvarcharintocol;

7) Write a stored procedure and put clob into the column

Create or replace PROCEDURE putCLOBINTOcol Rochelle clob; Rochelle start number: = 1; L_end number: = 32767; Rochelle amount number: = 32767; Rochelle buffer varchar2 (32767 ); Rochelle 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 ); Rochelle end: = instr (l_clob, ';', Rochelle start ); While (l_start <l_len) Loop -- Dbms_output.put_line ('L _ start = '| l_start |', l_end = '| l_end ); Rochelle 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; Rochelle start: = Rochelle end + 1; Rochelle end: = instr (l_clob, ';', Rochelle start ); End loop; END putCLOBINTOcol;

3. Note: because there are more than 90 columns, the length of the string may exceed 32767, which requires additional processing.