I have a problem to solve: I have a table in Oracle 11g that stores the company entry notes, in this table I have a column of type CLOB that stores the complete XML file of the note, my mission is: get this column of the CLOB type with the XML and export the xml to some folder on my computer. That is, make a cursor, grab the XML in that column and export one by one to a folder in my micro ... However I am not able to perform this exportation, I am trying with the following code:
DECLARE
T_OUT_FILE UTL_FILE.FILE_TYPE;
T_BUFFER CLOB; --VARCHAR2(32767);
T_AMOUNT BINARY_INTEGER := 10000;
T_POS INTEGER := 1;
T_CLOB_LEN INTEGER;
P_DATA CLOB;
P_DIR VARCHAR2(100) := 'DIR_BI_XML';
P_FILE VARCHAR2(100);
conta INT := 0;
erros INT := 0;
BEGIN
FOR R_ IN (SELECT A.RCNF_ST_NOMEFANTASIA || '-' || A.RCB_ST_NOTA || '-' || TO_CHAR(A.RCB_DT_DOCUMENTO,'DD-MM-YYYY') || '.XML' As NOME_ARQUIVO
,A.RCNF_ST_ARQUIVOXML,
a.agn_tab_in_codigo,
a.agn_pad_in_codigo,
a.agn_in_codigo,
a.rcb_st_nota,
a.rcb_dt_documento
FROM notas A
WHERE A.ORG_IN_CODIGO = 8
AND A.RCB_DT_DOCUMENTO BETWEEN '01/06/2017' AND '30/06/2017'
Order By A.RCB_DT_DOCUMENTO)
LOOP
Begin
P_FILE := R_.Nome_Arquivo;
P_DATA := R_.RCNF_ST_ARQUIVOXML;
T_CLOB_LEN := DBMS_LOB.GETLENGTH(P_DATA);
T_OUT_FILE := UTL_FILE.FOPEN(P_DIR,
P_FILE,
'W',
32767);
DBMS_OUTPUT.PUT_LINE(' length : ' || T_CLOB_LEN);
T_POS := 1;
DBMS_LOB.READ(P_DATA,
T_AMOUNT,
T_POS,
T_BUFFER);
UTL_FILE.PUT(T_OUT_FILE,
T_BUFFER);
UTL_FILE.FFLUSH(T_OUT_FILE);
T_POS := T_POS + T_AMOUNT;
DBMS_OUTPUT.PUT_LINE('completed writing');
UTL_FILE.FCLOSE(T_OUT_FILE);
EXCEPTION
When OTHERS THEN
IF(UTL_FILE.IS_OPEN(T_OUT_FILE)) THEN
UTL_FILE.FCLOSE(T_OUT_FILE);
DBMS_OUTPUT.put_line('Nota com erro: '||R_.RCB_ST_NOTA||' data: '||r_.rcb_dt_documento||' Agente: '||r_.agn_in_codigo);
erros := erros +1;
END IF;
DBMS_OUTPUT.put_line('Ia cair no Raise '||R_.RCB_ST_NOTA||' data: '||r_.rcb_dt_documento||' Agente: '||r_.agn_in_codigo);
--RAISE;
END;
conta := conta + 1;
END LOOP;
dbms_output.put_line(conta);
dbms_output.put_line(erros);
END;
However, this code exports only the XML file header of each note. Thank you!