.I have flat file inside a clob field and structure of the flat-file something like as below. and flat files contain 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.
DECLARE
n varchar2(32000) := 1;
nStartIndex NUMBER := 1;
nEndIndex NUMBER := 1;
nLineIndex NUMBER := 0;
vLine VARCHAR2(2000);
CURSOR c_clob
IS
SELECT char_data FROM Clob_Data_Table WHERE seqnum=11607;
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
-- DBMS_OUTPUT.ENABLE(1000000);
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);
-- dbms_output.put_line(c);
-- INSERT
-- INTO dumm VALUES(c);
-- commit;
END LOOP;
CLOSE c_clob;
END;
Currently I am using above code
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_.Need to Write CLOB file data into ORACLE table with PLSQL