Help extracting Clob data from a column in a table that has multiple rows in a table
this works for only the first row
CREATE OR REPLACE DIRECTORY XMLDIR AS '/orabackups';
grant read,write on DIRECTORY XMLDIR to public;
dbms_xslprocessor.clob2file(
buf IN CLOB,
XMLDIR IN VARCHAR2,
'testfile2.txt' IN VARCHAR2);
DECLARE
buf CLOB;
BEGIN
SELECT H15_DOC
INTO buf
FROM H15TEST.H15_STAGE
where rownum=1;
dbms_xslprocessor.clob2file(buf, 'XMLDIR', 'testfile2.txt');
END;
/
This code errors out
CREATE OR REPLACE DIRECTORY XMLDIR AS '/orabackups';
grant read,write on DIRECTORY XMLDIR to public;
dbms_xslprocessor.clob2file(
xdata IN CLOB,
XMLDIR IN VARCHAR2,
'testfile2.txt' IN VARCHAR2,
csid IN NUMBER := 0);
DECLARE
CURSOR xmlmycur IS SELECT H15_DOC
FROM H15TEST.H15_STAGE
where rownum = 102140;
l_clob CLOB;
xdata CLOB;
BEGIN
dbms_lob.createtemporary( l_clob,true );
dbms_lob.createtemporary( xdata,true );
OPEN xmlmycur;
LOOP
FETCH xmlmycur INTO xdata;
dbms_xslprocessor.clob2file(xdata, 'XMLDIR', 'testfile2.txt');
EXIT WHEN xmlmycur%notfound;
END LOOP;
CLOSE xmlmycur;
END;
/
ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 991
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 324
ORA-06512: at line 15