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
0 comments:
Post a Comment