Witam,<\/p>\n
Mamy tabl\u0119 kt\u00f3ra ma kolumn\u0119 CLOB. W kolumnie jest xml kt\u00f3ry mo\u017ce pos\u0142u\u017cy\u0107 nam do raportu w BIP( nie musimy robi\u0107 \u017cadnych sqli w xmlu mamy wszystkie dane do raportu).
\n1. Rejestrujemy \u017ar\u00f3d\u0142o danych jako pakiet pl\/sql
\n2. W \u017ar\u00f3dle danych mamy procedure kt\u00f3ra nam pobiera xmla i generuje go na wyjscie raportu.<\/p>\n
Informacje o pozosta\u0142ym setupu znajdziemy http:\/\/ora-apps.blogspot.com\/2007\/10\/pl-sql-stored-procedure-xml-publisher.html<\/a><\/p>\n Pozdrawiam, Witam, Mamy tabl\u0119 kt\u00f3ra ma kolumn\u0119 CLOB. W kolumnie jest xml kt\u00f3ry mo\u017ce pos\u0142u\u017cy\u0107 nam do raportu w BIP( nie musimy robi\u0107 \u017cadnych sqli w xmlu mamy wszystkie dane do raportu). 1. Rejestrujemy \u017ar\u00f3d\u0142o danych jako pakiet pl\/sql 2. W … Czytaj dalej \r\nPROCEDURE xx_sra_gen_pdf(p_error_code OUT NUMBER,\r\n p_error_msg OUT VARCHAR2,\r\n p_payment_instruction_id in number,\r\n p_payment_reference_number in number) IS\r\n l_payment CLOB;\r\n l_xml CLOB;\r\n l_end NUMBER;\r\n l_clob_size INTEGER;\r\n l_offset INTEGER := '1'; --idziemy od 1 znaku XMLa\r\n l_chunk_size INTEGER := '28000'; -- wycinamy 28000 powinno by\u0107 32K \r\n--nie ma substb dlatego jest ich mniej\r\n \r\n BEGIN\r\n FND_FILE.PUT_line(FND_FILE.LOG,\r\n 'p_payment_instruction_id ' ||\r\n p_payment_instruction_id);\r\n FND_FILE.PUT_line(FND_FILE.LOG,\r\n 'p_payment_reference_number ' ||\r\n p_payment_reference_number);\r\n --wlaczenie zabeczpieczen by moc odczytac dane z tabeli iby_xml_fd_pmt_1_0_v\r\n cep_standard.init_security; -- inicjowanie zabezpieczen dla zobowizan.\r\n SELECT XMLTYPE.getClobVal(payment)\r\n INTO l_payment\r\n FROM iby_xml_fd_pmt_1_0_v\r\n WHERE payment_instruction_id = p_payment_instruction_id\r\n AND payment_reference_number = p_payment_reference_number;\r\n \r\n l_clob_size := dbms_lob.getlength(l_payment);\r\n -- FND_FILE.PUT_line(FND_FILE.LOG, 'Rozmiar l_clob_size ' || l_clob_size);\r\n \r\n WHILE (l_clob_size > 0) LOOP\r\n \r\n \r\n l_xml := dbms_lob.substr(l_payment, l_chunk_size, l_offset);\r\n l_end := instr(l_xml, '>', -1);\r\n l_xml := dbms_lob.substr(l_payment, l_end, l_offset);\r\n FND_FILE.PUT_line(FND_FILE.OUTPUT, l_xml);\r\n l_chunk_size := l_end;\r\n l_clob_size := l_clob_size - l_chunk_size;\r\n l_offset := l_offset + l_chunk_size;\r\n END LOOP;\r\n EXCEPTION\r\n WHEN TOO_MANY_ROWS THEN\r\n DBMS_OUTPUT.put_line('TO_MANY_ROWS' || SQLERRM);\r\n WHEN NO_DATA_FOUND THEN\r\n DBMS_OUTPUT.put_line('NO_DATA_FOUND' || SQLERRM);\r\n WHEN OTHERS THEN\r\n DBMS_OUTPUT.put_line('EXCEPTION WHEN OTHERS THEN' || SQLERRM);\r\n END xx_sra_gen_pdf;\r\n<\/pre>\n
\nLuko<\/p>\n","protected":false},"excerpt":{"rendered":"