In many enterprise applications built with Oracle APEX, generating reports dynamically is a common requirement. One powerful approach is integrating Oracle Reports with APEX and launching them directly from JavaScript.

In this article, we will build a fully dynamic report URL generator system, store configuration in a table, and open reports using Dynamic Actions + JavaScript.

🧱 Step 1: Create Report Parameter Table

We store all report configuration in a single table so we can manage it without touching code.


CREATE TABLE REPPARA 
(
    ID         NUMBER(10,0),
    IP         VARCHAR2(30),
    PORT       VARCHAR2(30),
    URL        VARCHAR2(30),
    CMDKEY     VARCHAR2(30),
    DESFORMAT  VARCHAR2(30),
    DESTYPE    VARCHAR2(30)
);

📥 Step 2: Insert Configuration Data


INSERT INTO REPPARA 
(ID, IP, PORT, URL, CMDKEY, DESFORMAT, DESTYPE)
VALUES  
(1, '123.176.58.166', '8889', '/reports/rwservlet', 'hr', 'pdf', 'cache');

COMMIT;

⚙️ Step 3: Create PL/SQL Function to Build Report URL

This function dynamically generates the Oracle Reports URL.


CREATE OR REPLACE FUNCTION F_PRINT_PARA
(
    vid        NUMBER,
    vPath      VARCHAR2,
    vReportNM  VARCHAR2,
    vParamForm VARCHAR2
) RETURN VARCHAR2
IS
    rep_url     VARCHAR2(2000);
    vIP         VARCHAR2(30);
    vPORT       VARCHAR2(30);
    vURL        VARCHAR2(200);
    vCMDKEY     VARCHAR2(30);
    vDESFORMAT  VARCHAR2(30);
    vDESTYPE    VARCHAR2(30);
BEGIN
    SELECT IP, PORT, URL, CMDKEY, DESFORMAT, DESTYPE
    INTO   vIP, vPORT, vURL, vCMDKEY, vDESFORMAT, vDESTYPE
    FROM   REPPARA
    WHERE  ID = vid;

    rep_url :=
        'http://' || vIP || ':' || vPORT || vURL ||
        '?cmdkey='    || vCMDKEY ||
        '&report='     || vPath || vReportNM ||
        '&desformat='  || vDESFORMAT ||
        '&destype='    || vDESTYPE ||
        '&paramform='  || vParamForm;

    RETURN rep_url;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

🧠 Step 4: Oracle APEX Dynamic Action (Server Side)

In your APEX page (example: Page 3), create a Dynamic Action.

🔹 Server-side Code (PL/SQL)


:P3_REPPARA := F_PRINT_PARA(1, 'C:/REP/', 'EMPLOYEES.jsp', 'no');

This stores the generated URL in page item P3_REPPARA.

💻 Step 5: JavaScript to Open Report

Now open the report using JavaScript:


var r_link = $v("P3_REPPARA");
window.open(r_link, '_blank');

🌐 Final Flow

User clicks button in APEX
Dynamic Action runs PL/SQL function
URL is generated dynamically
URL stored in page item
JavaScript reads it
Report opens in new tab

🔥 Why This Approach is Powerful

✔ Centralized configuration

No need to hardcode IP, port, or report path.

✔ Easy maintenance

Change report server → update only table.

✔ Scalable

You can support multiple reports using different IDs.

✔ Clean separation

PL/SQL → builds logic
JavaScript → handles UI
Table → holds configuration

⚠️ Important Notes

Ensure Oracle Reports server is running on port 8889
Make sure firewall allows access
If using HTTPS, update URL accordingly
Use proper authentication if required

Passing Parameters to Oracle Reports from Oracle APEX

You can also pass dynamic parameters from Oracle APEX page items directly into your Oracle Report URL.

For example, suppose you want to print employees based on department number.

📌 Server-Side PL/SQL Code

In your Dynamic Action → Execute Server-side Code:


:P3_REPPARA := 
    F_PRINT_PARA(
        1,
        'C:/REP/',
        'EMPLOYEES_DEPT.jsp',
        'no'
    ) || '&DEPT_ID=' || :P3_DEPTNO;

💻 JavaScript Code

Then add another True Action → Execute JavaScript Code


var r_link = $v("P3_REPPARA");
window.open(r_link, '_blank');

🔥 Generated URL Example

If:


P3_DEPTNO = 10

Then the generated URL becomes:


http://123.176.58.166:8889/reports/rwservlet
?cmdkey=hr
&report=C:/REP/EMPLOYEES_DEPT.jsp
&desformat=pdf
&destype=cache
&paramform=no
&DEPT_ID=10

✅ How It Works

Step 1

User selects department number.

Step 2

PL/SQL dynamically appends:


&DEPT_ID=10

Step 3

JavaScript opens the report URL.

Step 4

Oracle Reports receives parameter:


DEPT_ID

and filters the report data accordingly.

🚀 Advanced Example (Multiple Parameters)


:P3_REPPARA :=
    F_PRINT_PARA(
        1,
        'C:/REP/',
        'EMPLOYEES_DEPT.jsp',
        'no'
    )
    || '&DEPT_ID=' || :P3_DEPTNO
    || '&EMP_ID='  || :P3_EMPNO
    || '&STATUS='  || :P3_STATUS;

⚠️ Important Tips

✅ Item Names Must Match

Oracle Report parameter names must exactly match:


DEPT_ID
EMP_ID
STATUS

✅ Submit Page Items

In Dynamic Action → Server-side Code:

Add items to:


Items to Submit

Example:


P3_DEPTNO,P3_EMPNO,P3_STATUS

Otherwise APEX may not send updated values to session state.

🎯 Result

You now have a fully dynamic Oracle Reports integration with:

Dynamic report names
Dynamic parameters
JavaScript popup opening
Reusable architecture

Conclusion

This architecture gives you a professional-grade reporting system inside Oracle APEX using dynamic configuration and JavaScript integration.

It is highly useful for:

ERP systems
HR modules
Financial reports
Distribution systems

Raw Source :

Create table 1st :

CREATE TABLE "REPPARA"

( "ID" NUMBER(10,0),

"IP" VARCHAR2(30),

"PORT" VARCHAR2(30),

"URL" VARCHAR2(30),

"CMDKEY" VARCHAR2(30),

"DESFORMAT" VARCHAR2(30),

"DESTYPE" VARCHAR2(30)

);

-- Insert values :

-------------------

insert into reppara (id,ip,port,url,cmdkey,desformat,destype)

values (1,'123.176.58.166','8889','/reports/rwservlet','hr','pdf','cache');

commit;

--- Create Function :

---------------------

create or replace FUNCTION F_PRINT_PARA

(

vid NUMBER,

vPath VARCHAR2,

vReportNM VARCHAR2,

vParamForm VARCHAR2

) RETURN VARCHAR2

rep_url VARCHAR2(2000);

vIP VARCHAR2(30);

vPORT VARCHAR2(30);

vURL VARCHAR2(200);

vCMDKEY VARCHAR2(30);

vDESFORMAT VARCHAR2(30);

vDESTYPE VARCHAR2(30);

BEGIN

SELECT IP,PORT,URL,CMDKEY,DESFORMAT,DESTYPE

INTO vIP,vPORT,vURL,vCMDKEY,vDESFORMAT,vDESTYPE

FROM policy.reppara

WHERE id = vid;

rep_url :=

'http://' || vIP || ':' || vPORT || vURL ||

'?cmdkey=' || vCMDKEY ||

'&report=' || vPath || vReportNM ||

'&desformat=' || vDESFORMAT ||

'&destype=' || vDESTYPE ||

'&paramform=' || vParamForm;

RETURN rep_url;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN NULL;

END;

Without Parameter :

Now create a Dynamic Action in Oracle Apex :

server-side code :

:P3_REPPARA:=F_PRINT_PARA(1,'C:/REP/','EMPLOYEES.jsp','no');

Execute JavaScript:

var r_link = $v("P3_REPPARA");

window.open(r_link,'_blank');

With Parameter :

Server Side Code :

:P3_REPPARA:=F_PRINT_PARA(1,'C:/REP/','EMPLOYEES_DEPT.jsp','no')||'&DEPT_ID='||:P3_DEPTNO;

Execute JavaScript :