Call Oracle 10g Report Form Oracle Apex Application..

Purpose : I would like to show an 10g oracle report using Oracle Application Express.

Action :
1. Create a table for contain Report URL Information.
2. Create a procedure for return Return Report URL.
3. Create an Oracle Apex Form With 3 Item (Named P12_FROM_DATE, P12_TO_DATE, P12_URL).
4. Create an process Before Header (Name url_assigne)
5. Create an Branch After Processing (Name SHOW_REPORT)
6. Create an Button (SHOW)


1. Create a table for contain Report URL Information.
=========================================================
CREATE TABLE SY_REPORT_CONFIGURATION
(REPORT_SERVER VARCHAR2(30),
 MODULE_NAME    VARCHAR2(30),
 REPORT_EXTENSTION VARCHAR2(10),
 REPORT_FORMAT      VARCHAR2(10),
 REPORT_KEY_NAME    VARCHAR2(30),
 REPORT_FILE_PATH   VARCHAR2(200),
 REPORT_URL             VARCHAR2(300)
)

SET DEFINE OFF;
Insert into IMS.SY_REPORT_CONFIGURATION
   (REPORT_SERVER, MODULE_NAME, REPORT_EXTENSTION, REPORT_FORMAT, REPORT_KEY_NAME, REPORT_FILE_PATH, REPORT_URL)
 Values
   ('IMS', 'IMS', 'RDF', 'PDF', 'IMS',
    'G:\', 'http://10.11.201.200:7778/reports/rwservlet?');
COMMIT;


2. Create a procedure for return Return Report URL.
==========================================================

CREATE OR REPLACE PROCEDURE DPR_REPORT_RUN
(P_MODULE_NAME IN VARCHAR2,
 P_REPORT_FILE_NAME VARCHAR2,
 P_OUTPUE    OUT VARCHAR2)
IS
V_DATA1 VARCHAR2(300);
V_REPORT_SERVER  VARCHAR2(300);
V_MODULE_NAME    VARCHAR2(30);
V_REPORT_EXTENSTION  VARCHAR2(300);
V_REPORT_FORMAT  VARCHAR2(300);
V_REPORT_KEY_NAME  VARCHAR2(300);
V_REPORT_FILE_PATH  VARCHAR2(300);
V_REPORT_URL  VARCHAR2(300);
BEGIN

SELECT LOWER(REPORT_SERVER), LOWER(MODULE_NAME), LOWER(REPORT_EXTENSTION), LOWER(REPORT_FORMAT), LOWER(REPORT_KEY_NAME), LOWER(REPORT_FILE_PATH), LOWER(REPORT_URL)
INTO V_REPORT_SERVER,V_MODULE_NAME , V_REPORT_EXTENSTION, V_REPORT_FORMAT, V_REPORT_KEY_NAME, V_REPORT_FILE_PATH, V_REPORT_URL
FROM SY_REPORT_CONFIGURATION
WHERE LOWER(MODULE_NAME)=LOWER(P_MODULE_NAME);

V_DATA1 := V_REPORT_URL||V_REPORT_KEY_NAME||'&desformat=pdf&destype=cache&paramform=NO&report='||V_REPORT_FILE_PATH||P_REPORT_FILE_NAME;


EXCEPTION
       WHEN NO_DATA_FOUND THEN
                V_DATA1:='URL NOT FOUND '||LOWER(P_MODULE_NAME);
       WHEN TOO_MANY_ROWS THEN
                V_DATA1:='TOO MANY URL FOUND '||LOWER(P_MODULE_NAME);
P_OUTPUE :=V_DATA1;

END;
/

4. Create an process Before Header (Name url_assigne)
==================================================
 Process Code

BEGIN
DPR_REPORT_RUN
(P_MODULE_NAME         =>'IMS',
 P_REPORT_FILE_NAME =>'COMPAY_INFO',
 P_OUTPUE                        =>:P12_URL);
END;

5. Create an Branch After Processing (Name SHOW_REPORT)
==================================================
Target Type : URL

URL Target: &P12_URL.&P_FROM_DATE=&P12_FROM_DATE.&P_TO_DATE=&P12_TO_DATE.

6. Create an Button (SHOW)

Change Button Action : Submit Page

Now Click Show button and see your report ........