How to import the csv file to the Clob field of the database in Apex and then to the corresponding column

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 the clob field CREATETABLETABLE3 (CONTENTCLOB); 2

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 the clob field create table TABLE3 (content clob); 2

1. Requirements

One user data exists in a 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,1b2,2c33,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 writecsvintoclob ASl_max_line_length integer := 32767;l_buffer varchar2(32767);l_file UTL_FILE.FILE_TYPE;l_clob clob;BEGINl_file := utl_file.fopen('CSV', 'book1.csv', 'r', l_max_line_length);dbms_lob.createtemporary(l_clob, TRUE, DBMS_LOB.session);loopbeginutl_file.get_line(l_file, l_buffer);dbms_lob.append(l_clob, l_buffer||';');exceptionwhen no_data_found thenexit;end;end loop;insert into table3 (content) values (l_clob);dbms_lob.freetemporary(l_clob);UTL_FILE.FCLOSE(l_file);END writecsvintoclob;

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) ASl_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); BEGINl_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
 
  
7) Write a stored procedure and put clob into the column
  
Create or replace PROCEDURE putCLOBINTOcol ASl_clob clob; l_start number: = 1; l_end number: = 32767; l_amount number: = 32767; l_buffer varchar2 (32767); l_len number; I number: = 1; BEGINselect 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
   
    
3. Notes
    
Because there are more than 90 columns, the length of the string may exceed 32767, which requires additional processing.