Need to Write CLOB file data into ORACLE table with PLSQL

Question

I have flat file inside a clob field and structure of the flat-file something like as below. and flat files containx million of records.

col1,col2,col3,col4,col5,col6
A,B,C,,F,D
1,A,2,B,B,C

Traditional ways I can't use like

1-fetch the data from clob in excel or something and the load the data into table with sql-loader.

2-currently I am able to print clob file with below code.

OPEN c_clob;
LOOP
FETCH c_clob INTO c;
EXIT
WHEN c_clob%notfound;
printout(c);

but problem in above code is if I use this variable into insert statement then it gives error due to CLOB to VAR insertion.

INSERT INTO Table1 VALUES(c);
commit;

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 848239, maximum: 4000)

Is there any other option available to handle huge flat-file from clob field and dump into a table.

Currently I am using below code

declare nStartIndex number := 1; nEndIndex number := 1; nLineIndex number := 0; vLine varchar2(2000); cursor c_clob is select char_data from clob_table where seq=1022; c clob; procedure printout (p_clob in out nocopy clob) is offset number := 1; amount number := 32767; amount_last number := 0; len number := dbms_lob.getlength(p_clob); lc_buffer varchar2(32767); line_seq pls_integer := 1; -- For UNIX type file - replace CHR(13) to NULL CR char := chr(13); --CR char := NULL; LF char := chr(10); nCRLF number; sCRLF varchar2(2); b_finish boolean := true; begin sCRLF := CR || LF; nCRLF := Length(sCRLF); if ( dbms_lob.isopen(p_clob) != 1 ) then dbms_lob.open(p_clob, 0); end if; amount := instr(p_clob, sCRLF, offset); while ( offset < len ) loop -- For without CR/LF on end file If amount < 0 then amount := len - offset + 1; b_finish := false; End If; dbms_lob.read(p_clob, amount, offset, lc_buffer); If b_finish then lc_buffer := SUBSTR(lc_buffer,1,Length(lc_buffer)-1); End If; if (line_seq-1) > 0 then amount_last := amount_last + amount; offset := offset + amount; else amount_last := amount; offset := amount + nCRLF; end if; amount := instr(p_clob, sCRLF, offset); amount := amount - amount_last; dbms_output.put_line('Line #'||line_seq||': '||lc_buffer); line_seq := line_seq + 1; end loop; if ( dbms_lob.isopen(p_clob) = 1 ) then dbms_lob.close(p_clob); end if; exception when others then dbms_output.put_line('Error : '||sqlerrm); end printout; begin open c_clob; loop fetch c_clob into c; exit when c_clob%notfound; printout(c); end loop; close c_clob; end;

Here line printout(c); (4th last line in code) showing me clob data line by line untill buffer gets overflow.

Expected result: To read data from clob flat-file and insert rows into table column wise, That's I am trying to achieve. Constraints is Flat-Files contains millions of records.

Not sure I understand... you have a CLOB that contains CSV data, and you want to convert that into separate columns in another table? Or just copy the entire CLOB to another table? What data type is your column in `Table1`, and what the query behind your `c_clob` cursor? — Alex Poole, Mar 05 '15 at 10:30
Yes,Clob contains csv file. I want to convert that into separate columns in another table. `CURSOR c_clob IS SELECT char_data FROM table ;` — user1782212, Mar 05 '15 at 10:36
Then you'll need to read the CLOB line-by-line (see [the `dbms_lob` package](https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_lob.htm)), and then split each of those lines into tokens based on the comma delimiter. There are lots of examples of both parts, you just need to try to put them together. — Alex Poole, Mar 05 '15 at 10:39
Or [use a library](http://stackoverflow.com/q/16304638/266304) instead of creating your own. — Alex Poole, Mar 05 '15 at 10:45
@AlexPoole Can you provide any reference for DBMS_LOB usage and splitting rows into token. I am not able to find such working example. — user1782212, Mar 05 '15 at 11:01
I am able to read the data from `dbms_lob.read` by using given example at this link..(http://stackoverflow.com/questions/11647041/reading-clob-line-by-line-with-pl-sql) But problem arises when I insert these rows into table due to CLOB and Varchar datatype. **seems** `dbms_lob.read` **package reads data iteself in CLOB format** we can see it but can't insert into `varchar2` column row by row. — user1782212, Mar 05 '15 at 11:36
[`read` is passed a varchar2 or raw our parameter](https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_lob.htm#i999170). The variable you pass should be varchar2, not CLOB (`lc_buffer` in that linked answer). If you're using that variable to insert you shouldn't get that error. Maybe add your table structure and current code to the question? — Alex Poole, Mar 05 '15 at 12:30
@AlexPoole I have added the code in my earlier post, which I am using currently. Please see if you can help me out here... — user1782212, Mar 05 '15 at 14:23
OK, but don't you want to insert `lc_buffer` into your table rather than printing it out? Or eventually, split that into elements that you put into invidual columns? — Alex Poole, Mar 05 '15 at 14:29
@AlexPoole no just need to insert clob data into table column wise..thats it.. — user1782212, Mar 05 '15 at 15:41
Why can't you use SQL Loader? Your file appears to specify columns: do you want to shred the contents and insert into multiple columns? Do you want to insert each file record into its own table row? — APC, Mar 06 '15 at 07:27
@APC I am using SQL-Developer can't use SQL-Loader due to some user access constraints. _Flat files resides int Clob filed of table means there may be many flat files. So I need to fetch flat file and put it's data column by column into other separate table_ — user1782212, Mar 06 '15 at 10:32

score 0 · Answer 1 · answered Mar 06 '15 at 12:02

i'm using something like this

...
select * into ifile from clob_table where ...;
file_length  := dbms_lob.getlength (ifile.char_data);
p_start :=1;
while p_start<>0 loop
    end_pos := dbms_lob.instr (ifile.char_data, chr (10), p_start);
    if end_pos > 0 then
        strRow := dbms_lob.substr(ifile.char_data, least (end_pos - p_start, 240), p_start),chr (13)||chr (10);
        p_start := end_pos + 1;
        tabRow := strRow2tabRow(strRow);
    else
        strRow := dbms_lob.substr (ifile.char_data, file_length - p_start + 1,  p_start);
        p_start := 0;
        tabRow := strRow2tabRow(strRow);
    end if;

insert into myTable values tabRow;
end loop;
...

and functions

function strRow2tabRow(strRow varchar2) return myTable%rowtype is
    tabRow myTable%rowtype;
begin
    tabRow.col1:=valueIncolumn(strRow,1);
    tabRow.col2:=valueIncolumn(strRow,2);
    ...
    /*or maybe this may be better for you
    select * into tabRow from (
        select rownum rn, regexp_substr(strRow,'[^,]+', 1, level) hdn from dual connect by regexp_substr(strRow, '[^,]+', 1, level) is not null 
        ) pivot (max(hdn) for rn in (1, 2, ...));
    */      
    return tabRow;
exception when others then
    return tabRow;
end; 

function valueIncolumn(strRow varchar2, pos in number) return varchar2 is
    ret varchar2(1024);
begin
    select hdn into ret from (
        select rownum rn, regexp_substr(strRow,'[^,]+', 1, level) hdn from dual connect by regexp_substr(strRow, '[^,]+', 1, level) is not null 
    ) where rownum=pos;
    return ret;
exception when others then 
    return null;
end;

hope it helps

Can't we handle >32K data from PLSQL...There should be a way in PLSQL for that ...Above method is failing :(.. Appreciate all of yours help.. — user1782212, Mar 06 '15 at 16:18

Need to Write CLOB file data into ORACLE table with PLSQL

1 Answers1