BIP – XML clob jako źródło danych do raportu w BIP/XML Publisher

PROCEDURE xx_sra_gen_pdf(p_error_code               OUT NUMBER,
                           p_error_msg                OUT VARCHAR2,
                           p_payment_instruction_id   IN NUMBER,
                           p_payment_reference_number IN NUMBER) IS
    l_payment    CLOB;
    l_xml        CLOB;
    l_end        NUMBER;
    l_clob_size  INTEGER;
    l_offset     INTEGER := '1'; --idziemy od 1 znaku XMLa
    l_chunk_size INTEGER := '28000'; -- wycinamy  28000 powinno być 32K 
--nie ma substb dlatego jest ich mniej
 
  BEGIN
    FND_FILE.PUT_line(FND_FILE.LOG,
                      'p_payment_instruction_id ' ||
                      p_payment_instruction_id);
    FND_FILE.PUT_line(FND_FILE.LOG,
                      'p_payment_reference_number ' ||
                      p_payment_reference_number);
    --wlaczenie zabeczpieczen by moc odczytac dane z tabeli iby_xml_fd_pmt_1_0_v
    cep_standard.init_security; -- inicjowanie zabezpieczen dla zobowizan.
    SELECT XMLTYPE.getClobVal(payment)
      INTO l_payment
      FROM iby_xml_fd_pmt_1_0_v
     WHERE payment_instruction_id = p_payment_instruction_id
       AND payment_reference_number = p_payment_reference_number;
 
    l_clob_size := dbms_lob.getlength(l_payment);
    -- FND_FILE.PUT_line(FND_FILE.LOG, 'Rozmiar l_clob_size ' || l_clob_size);
 
    WHILE (l_clob_size > 0) LOOP
 
 
      l_xml := dbms_lob.substr(l_payment, l_chunk_size, l_offset);
      l_end := instr(l_xml, '>', -1);
      l_xml := dbms_lob.substr(l_payment, l_end, l_offset);
      FND_FILE.PUT_line(FND_FILE.OUTPUT, l_xml);
      l_chunk_size := l_end;
      l_clob_size  := l_clob_size - l_chunk_size;
      l_offset     := l_offset + l_chunk_size;
    END LOOP;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.put_line('TO_MANY_ROWS' || SQLERRM);
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line('NO_DATA_FOUND' || SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('EXCEPTION WHEN OTHERS THEN' || SQLERRM);
  END xx_sra_gen_pdf;

PROCEDURE xx_sra_gen_pdf(p_error_code OUT NUMBER, p_error_msg OUT VARCHAR2, p_payment_instruction_id in number, p_payment_reference_number in number) IS l_payment CLOB; l_xml CLOB; l_end NUMBER; l_clob_size INTEGER; l_offset INTEGER := '1'; --idziemy od 1 znaku XMLa l_chunk_size INTEGER := '28000'; -- wycinamy 28000 powinno być 32K --nie ma substb dlatego jest ich mniej BEGIN FND_FILE.PUT_line(FND_FILE.LOG, 'p_payment_instruction_id ' || p_payment_instruction_id); FND_FILE.PUT_line(FND_FILE.LOG, 'p_payment_reference_number ' || p_payment_reference_number); --wlaczenie zabeczpieczen by moc odczytac dane z tabeli iby_xml_fd_pmt_1_0_v cep_standard.init_security; -- inicjowanie zabezpieczen dla zobowizan. SELECT XMLTYPE.getClobVal(payment) INTO l_payment FROM iby_xml_fd_pmt_1_0_v WHERE payment_instruction_id = p_payment_instruction_id AND payment_reference_number = p_payment_reference_number; l_clob_size := dbms_lob.getlength(l_payment); -- FND_FILE.PUT_line(FND_FILE.LOG, 'Rozmiar l_clob_size ' || l_clob_size); WHILE (l_clob_size > 0) LOOP l_xml := dbms_lob.substr(l_payment, l_chunk_size, l_offset); l_end := instr(l_xml, '>', -1); l_xml := dbms_lob.substr(l_payment, l_end, l_offset); FND_FILE.PUT_line(FND_FILE.OUTPUT, l_xml); l_chunk_size := l_end; l_clob_size := l_clob_size - l_chunk_size; l_offset := l_offset + l_chunk_size; END LOOP; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.put_line('TO_MANY_ROWS' || SQLERRM); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('NO_DATA_FOUND' || SQLERRM); WHEN OTHERS THEN DBMS_OUTPUT.put_line('EXCEPTION WHEN OTHERS THEN' || SQLERRM); END xx_sra_gen_pdf;