Sunday, February 21, 2016

Oracle XML Report - Using Data Source as PL/SQL Stored Procedure

1. Create a table
CREATE TABLE XXAA_PLSQL_XMLP_REPORT
( product_code NUMBER,
  product_name VARCHAR2 (100)
);

2. Insert Values
INSERT INTO XXAA_PLSQL_XMLP_REPORT VALUES (569, 'Oracle Cost Management');

3. Issue COMMIT;

4. Create a Package Spec & Body with a single Procedure
--> Spec:
CREATE OR REPLACE PACKAGE APPS.XXAA_PLSQL_XMLP_REPORT
AS
PROCEDURE REPORT
     (errbuf OUT VARCHAR2,
     retcode OUT VARCHAR2,
     p_product_id IN NUMBER
     );
END XXAA_PLSQL_XMLP_REPORT;
/

--> Body:
CREATE OR REPLACE PACKAGE BODY APPS.XXAA_PLSQL_XMLP_REPORT
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 XXAA_PLSQL_XMLP_REPORT
                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 XXAA_PLSQL_XMLP_REPORT.REPORT');

END REPORT;
END XXAA_PLSQL_XMLP_REPORT;

/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect