Background:
Developing sample XML Publisher Report with Executable Method as 'PL/SQL Stored Procedure'
In my previous post http://orclapp.blogspot.in/2011/11/using-data-template.html i have explained developing XML Publisher Report using Data Template as Data Source
CREATE TABLE demo_products
( product_code NUMBER,
product_name VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
VALUES (569, 'Oracle Cost Management');
3. Issue Commit
1. Create a Package Spec & Body with a single Procedure
Spec:
CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER);
END MY_PACKAGE;
/
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER)
IS
l_qryCtx DBMS_XMLGEN.ctxHandle;
l_query VARCHAR2 (32000);
l_length NUMBER (10);
l_xmlstr VARCHAR2 (32000);
l_offset NUMBER (10) := 32000;
l_retrieved NUMBER (10) := 0;
l_result CLOB;
l_no_rows NUMBER;
BEGIN
l_query := 'SELECT product_code, product_name
FROM demo_products
WHERE product_code = ' || p_product_id;
l_qryCtx := DBMS_XMLGEN.newContext (l_query);
-- set rowset tag to PRODUCTS and row tag to PRO_DETAILS
DBMS_XMLGEN.setRowSetTag (l_qryCtx, 'PRODUCTS');
DBMS_XMLGEN.setRowTag (l_qryCtx, 'PRO_DETAILS');
-- now get the result
l_result := DBMS_XMLGEN.getXML (l_qryCtx);
l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
FND_FILE.put_line (FND_FILE.LOG, 'No of rows processed= ' || l_no_rows);
l_length := NVL (DBMS_LOB.getlength (l_result), 0);
FND_FILE.put_line (FND_FILE.LOG, 'Length= ' || l_length);
LOOP
EXIT WHEN l_length = l_retrieved;
IF (l_length - l_retrieved) < 32000
THEN
SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;
l_retrieved := l_length;
fnd_file.put_line (fnd_file.output, l_xmlstr);
ELSE
SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
INTO l_xmlstr
FROM DUAL;
l_retrieved := l_retrieved + l_offset;
fnd_file.put_line (fnd_file.output, l_xmlstr);
END IF;
END LOOP;
DBMS_XMLGEN.closeContext (l_qryCtx);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
raise_application_error (-20001, 'Error in procedure MY_PACKAGE.report');
END REPORT;
END MY_PACKAGE;
2. Define Executable
Navigation: Application Developer > Concurrent > Executable
Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: MY_PACKAGE.REPORT
3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program
Provide Program, Short name, Application, Description & also
- Executable Name as defined in the above step
- Output Format should be XML
- Define a Parameter p_product_id
- Associate Concurrent Program to the Request Group.
4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.
5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template
6. Run the Concurrent Program to see the output
Developing sample XML Publisher Report with Executable Method as 'PL/SQL Stored Procedure'
In my previous post http://orclapp.blogspot.in/2011/11/using-data-template.html i have explained developing XML Publisher Report using Data Template as Data Source
Prerequisite for the below Example:
1. Create a tableCREATE TABLE demo_products
( product_code NUMBER,
product_name VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
VALUES (569, 'Oracle Cost Management');
3. Issue Commit
1. Create a Package Spec & Body with a single Procedure
Spec:
CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER);
END MY_PACKAGE;
/
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
PROCEDURE REPORT (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_product_id IN NUMBER)
IS
l_qryCtx DBMS_XMLGEN.ctxHandle;
l_query VARCHAR2 (32000);
l_length NUMBER (10);
l_xmlstr VARCHAR2 (32000);
l_offset NUMBER (10) := 32000;
l_retrieved NUMBER (10) := 0;
l_result CLOB;
l_no_rows NUMBER;
BEGIN
l_query := 'SELECT product_code, product_name
FROM demo_products
WHERE product_code = ' || p_product_id;
l_qryCtx := DBMS_XMLGEN.newContext (l_query);
-- set rowset tag to PRODUCTS and row tag to PRO_DETAILS
DBMS_XMLGEN.setRowSetTag (l_qryCtx, 'PRODUCTS');
DBMS_XMLGEN.setRowTag (l_qryCtx, 'PRO_DETAILS');
-- now get the result
l_result := DBMS_XMLGEN.getXML (l_qryCtx);
l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
FND_FILE.put_line (FND_FILE.LOG, 'No of rows processed= ' || l_no_rows);
l_length := NVL (DBMS_LOB.getlength (l_result), 0);
FND_FILE.put_line (FND_FILE.LOG, 'Length= ' || l_length);
LOOP
EXIT WHEN l_length = l_retrieved;
IF (l_length - l_retrieved) < 32000
THEN
SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;
l_retrieved := l_length;
fnd_file.put_line (fnd_file.output, l_xmlstr);
ELSE
SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
INTO l_xmlstr
FROM DUAL;
l_retrieved := l_retrieved + l_offset;
fnd_file.put_line (fnd_file.output, l_xmlstr);
END IF;
END LOOP;
DBMS_XMLGEN.closeContext (l_qryCtx);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
raise_application_error (-20001, 'Error in procedure MY_PACKAGE.report');
END REPORT;
END MY_PACKAGE;
/
Navigation: Application Developer > Concurrent > Executable
Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: MY_PACKAGE.REPORT
3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program
Provide Program, Short name, Application, Description & also
- Executable Name as defined in the above step
- Output Format should be XML
- Define a Parameter p_product_id
- Associate Concurrent Program to the Request Group.
4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.
5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template
6. Run the Concurrent Program to see the output
No comments:
Post a Comment