Generate field Xml file in Oracle

0

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!

    
asked by anonymous 07.02.2018 / 11:34

0 answers