Good afternoon! I have a problem at work to solve and I would like to know if there is any prepared script that can help me, I need to grab the XML file from input notes that are stored in a clob-like column in a table in the database and export them for a certain folder on my computer in XML format, however I am not able to do it, it follows a script that I am trying to use but it is doing some kind of undue looping because it should only bring 1312 records, but at the time of the loop it is bringing much more:
DECLARE
T_OUT_FILE UTL_FILE.FILE_TYPE;
T_BUFFER VARCHAR2(32767);
T_AMOUNT BINARY_INTEGER := 1000;
T_POS INTEGER := 1;
T_CLOB_LEN INTEGER;
P_DATA CLOB;
P_DIR VARCHAR2(100) := 'DIR_BI_XML';
P_FILE VARCHAR2(100);
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 MGADM.EST_RECEBIMENTONFE A
WHERE A.ORG_IN_CODIGO = 8
AND A.RCB_DT_DOCUMENTO BETWEEN '01/01/2017' AND '31/01/2017'
AND A.RCB_ST_NOTA NOT IN (SELECT B.RCB_ST_NUMERO FROM mgobr.tmp_xml_impresso_edu B)
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;
WHILE T_POS < T_CLOB_LEN
LOOP
--LOG
INSERT INTO mgobr.tmp_xml_impresso_edu VALUES (
r_.rcb_st_nota, r_.rcb_dt_documento, r_.agn_tab_in_codigo, r_.agn_pad_in_codigo, r_.agn_in_codigo, SYSDATE, 'S');
COMMIT;
DBMS_OUTPUT.PUT_LINE(' t_pos before : ' || T_POS);
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);
DBMS_OUTPUT.PUT_LINE(' t_pos after : ' || T_POS);
T_POS := T_POS + T_AMOUNT;
END LOOP;
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);
END IF;
P_SENDMAIL (from_name => '[email protected]', from_address => '[email protected]',to_name => '[email protected]',message => 'Problemas para gerar XML
da NOTA : '||R_.RCB_ST_NOTA||' agente: '||r_.agn_in_codigo);
--RAISE;
END;
END LOOP;
END;
I want to be able to do this in a simple way,