How to Call Report from forms in Oracle Fusion Middleware 11g

In order to run Report from Forms:

You may want to call report from forms with or without parameter.

You must call the following script from Forms:

Without Parameter:

DECLARE
         rep_url varchar2(2000);
         vDB     varchar2(10);
BEGIN
  :GLOBAL.PATH:='D:\PROJECTS1\PROJECT\BIN\';
  vDB:=GET_APPLICATION_PROPERTY(CONNECT_STRING);
  rep_url:='http://DESKTOP-B2K7G7D:9002/reports/rwservlet?'||'report='||:GLOBAL.PATH||'deptlist.rep'||'&desformat=pdf&destype=cache'||'&userid=HR/HR@'||vDB||' paramform=no';
  WEB.SHOW_DOCUMENT(rep_url,'_blank');
END;

With Parameter:

DECLARE
         rep_url varchar2(2000);
         vDB     varchar2(10);
BEGIN
         :GLOBAL.PATH:='D:\PROJECTS1\PROJECT\BIN\';
         vDB:=GET_APPLICATION_PROPERTY(CONNECT_STRING);
         rep_url:='http://DESKTOP-B2K7G7D:9002/reports/rwservlet?'||'report='||:GLOBAL.PATH||'REP.rep'||'&desformat=pdf&destype=cache'||'&userid=HR/HR@'||vDB||' paramform=no P_DEPTNO='||:REP.DEPTNO||' P_SALARY='||:SALARY;
  WEB.SHOW_DOCUMENT(rep_url,'_blank');
END;

:GLOBAL.PATH --------- PATH OF REPORTS.

:vDB  ---------------             Get Connect String or Database Name from GET_APPLICATION_PROPERTY(CONNECT_STRING);

rep_url          ------------- Variable that denotes URL of Report.
WEB.SHOW_DOCUMENT ----------  Show Web Report.
_Blank        ------------- Open Report in Separate window.

http://    -------> http
DESKTOP-B2K7G7D ----------- Host name or IP Address.
9002 --------- Report Port.
/reports/rwservlet?    --------- Default path of report.
deptlist.rep     -------- Name of report.
desformat ------------ pdf/htmlcss/spreadsheet
destype ----------- cache/printer
userid  --------- username and password with database name : HR/HR@'||vDB
paraform=no ------- Parameter Form Of Report will not display.
P_DEPTNO ------- Report Parameter.
P_SALARY ------- Report Parameter.
:REP.DEPTNO -------- Form Item.
:SALARY      --------- Form Item.

Download Sample.

You can create report based on parameter from table (Dynamic) :

You can also create a table :

You can create the table with the following:

create table reppara (
ID                NOT NULL NUMBER(10),
IP                         VARCHAR2(30),
PORT                       VARCHAR2(30),
URL                        VARCHAR2(30),
CMDKEY                     VARCHAR2(30),
DESFORMAT                  VARCHAR2(30),
DESTYPE                    VARCHAR2(30)
);

and follow the following procedure when calling report:

DECLARE
vDB     varchar2(10);
        rep_url varchar2(2000);

v_IP    varchar2(50);
v_port  varchar2(50);
v_cmdkey varchar2(50);
v_url    varchar2(50);
v_destype  varchar2(50);
v_desformat varchar2(50);
BEGIN 
:global.path:='x:\give\path\here\';

select IP,PORT,URL,CMDKEY,DESFORMAT,DESTYPE
        into          v_IP,v_PORT,v_URL,v_CMDKEY,v_DESFORMAT,v_DESTYPE
        from        reppara
        where id=1;    rep_url:='http://'||v_ip||':'||v_port||v_url||'?'||'report='||:GLOBAL.PATH||'EMP_DEPT_WISE.rep'||'&cmdkey='||v_cmdkey||'&desformat='||v_desformat||'&destype='||v_destype||'&paramform=no';

    WEB.SHOW_DOCUMENT(rep_url,'_blank');
END;

cmdkey configuration:

Go to the following path:

C:\Oracle\Middleware\user_projects\domains\ClassicDomain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_11.1.2\configuration

edit cgicmd.dat file.

add the following line at the bottom as username/password@database :

erp: Userid=ERP08/E@XE%*

Restart WLS_REPORT.