export XML files stored in oracle database

0

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,     

asked by anonymous 26.01.2018 / 21:10

0 answers