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¶mform=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 ........
