Package Specification:
CREATE OR REPLACE PACKAGE xx_plsql_xml_pkg
AS
PROCEDURE xxjs_main_proc (x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER);
END xx_plsql_xml_pkg;
/
Package Body:
CREATE OR REPLACE PACKAGE BODY xx_plsql_xml_pkg
AS
PROCEDURE xx_main_proc (x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER)
IS
l_qryctx DBMS_XMLGEN.ctxhandle;
l_result CLOB;
l_no_rows NUMBER;
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'XML Generating Started ................................'
);
l_qryctx := DBMS_XMLGEN.newcontext('select * from emp');
----Define your query here
LOOP
-- save the XML into the CLOB field
l_result := DBMS_XMLGEN.getxml(l_qryctx);
EXIT WHEN DBMS_XMLGEN.getnumrowsprocessed(l_qryctx) = 0;
-- store the XML as an output
fnd_file.put_line (fnd_file.output, l_result);
END LOOP;
DBMS_XMLGEN.closecontext (l_qryctx);
fnd_file.put_line
(fnd_file.LOG,
'XML Generating End ..................................'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Errors: ' || SQLERRM);
END xx_main_proc;
END xx_plsql_xml_pkg;
/
Create “Executable” for the report (XXPLSXMLREP)
Navigation: System Administrator or Application Development > Concurrent > Executable
Executable: XXPLSXMLREP
Short Name: XXPLSXMLREP
Application: XX Custom Application
Description: My Custom Report Using PL/SQL & BI Publisher
Executable Method: PL/SQL Stored Procedure
Executable File Name: XX_PLSQL_XML_PKG.XX_MAIN_PROC
Create “Concurrent Program” and attach this executable to this concurrent program and define parameters if required
Navigation: System Administrator or Application Development > Concurrent > Program
Program: My Custom Report Using PL/SQL & BI Publisher
Short Name: XXJSPLSXMLREP
Application: XXJS Custom Application
Description: My Custom Report Using PL/SQL
Executable Name: XXJSPLSXMLREP
Output Format: XML
Create “Request Group” or Attach Concurrent Program to existing Request Group
Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete