[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3269
  • Last Modified:

Integrating Oracle Reports with Oracle Forms 10g Menus

I just migrated from oracle developer 6.0 to oracle developer 10g....I have to call reports through Menus...in 6.0 I used the following code

Declare
pl_id paramlist;
Begin

if :global.hlr is null then
message('Cannot run report - No Hotline Record');
raise form_trigger_failure;
end if;

pl_id := Get_Parameter_List('p_list');
if not Id_Null(pl_id) then
Destroy_Parameter_List(pl_id);
end if;

pl_id := create_parameter_list('p_list');
add_parameter(pl_id,'hlr_num',text_parameter,:global.hlr);

run_product(reports,'hotline',synchronous,runtime,filesystem,pl_id,'null');

End;

in 10 g I changed the "run_product" as follows

Web.show_document ('/reports/rwservlet=Repsrv&report=C:\myApp\rpt.rdf&desformat=pdf&destype=cache&userid=user/password@orcl&paramform=yes&HLR_NUM=3171,'_blank');

for test purpose and the report came out blank...can some one please direct me on how to pass parameters.

Thank You
0
bsippy
Asked:
bsippy
  • 10
  • 10
1 Solution
 
Helena Markováprogrammer-analystCommented:
Here is my accepted answer to the similar question (http://www.experts-exchange.com/Web/Application_Servers/Oracle_iAS/Q_21384830.html):

declare
 report_id Report_Object;
 ReportServerJob           VARCHAR2(100);
 rep_status                    VARCHAR2(200);
 L_REPORT_SERVER       VARCHAR2(15)    :=:agenti.nume;
 L_REPORT_SERVER_PATH  VARCHAR2(30); --  if you want to run report in a development environment e.g. 'http://yourpc:8888/reports';
 L_REPORT_DESFORMAT    VARCHAR2(15):='HTML';
 rep_status                        VARCHAR2(200);
 report_job_id                    VARCHAR2(200);
BEGIN

 report_id:= find_report_object('REPORT6');
 SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_OTHER,'p_deptno='||:agenti.id_agent||' paramform=no');

 SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,'C:\Licenta\test.rdf');
 SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);
 SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,BATCH);
 SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,CACHE);
 SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,L_REPORT_DESFORMAT);
 SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,L_REPORT_SERVER);

    report_job_id := RUN_REPORT_OBJECT(report_id);

    rep_status:=REPORT_OBJECT_STATUS(report_job_id);

    WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')
    LOOP
       rep_status:=run_report_object(report_id);
    END LOOP;
    IF rep_status='FINISHED' THEN
-- this shows report
      WEB.SHOW_DOCUMENT(L_REPORT_SERVER_PATH||'/rwservlet/getjobid'||substr(report_job_id,instr(report_job_id,'_',-1)+1)||'?'||'server='||L_REPORT_SERVER,'_blank');
    ELSE
       message('Error');
    END IF;
END;
0
 
Helena Markováprogrammer-analystCommented:
Oh sorry, I have missed parameters, here it is:

Declare
 pl_id           ParamList;
 pl_name         VARCHAR2(10):='tempdata';

BEGIN
pl_id := Get_Parameter_List(pl_name);
 IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List( pl_id );
 END IF;
 pl_id := Create_Parameter_List(pl_name);

... adding parameters
... setting report object properties

-- here it is:
    report_job_id := RUN_REPORT_OBJECT(report_id,pl_id);

    report_job_id := RUN_REPORT_OBJECT(report_id);

    rep_status:=REPORT_OBJECT_STATUS(report_job_id);

    WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')
    LOOP
       rep_status:=run_report_object(report_id);
    END LOOP;
    IF rep_status='FINISHED' THEN
-- this shows report
      WEB.SHOW_DOCUMENT(L_REPORT_SERVER_PATH||'/rwservlet/getjobid'||substr(report_job_id,instr(report_job_id,'_',-1)+1)||'?'||'server='||L_REPORT_SERVER,'_blank');
    ELSE
       message('Error');
    END IF;
END;
0
 
bsippyAuthor Commented:
Thank You for the response.

I am calling my report from a Menu so how do I get the find_report_object()

Really appreciate ur response
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bsippyAuthor Commented:
I am getting the error

FRM-41213:Unable to connect to the Report server Repsrv@sec12-pc.


How can I proceed from here...I have my Repsrv server started and also the OC4J Instance.

Thank You.
0
 
bsippyAuthor Commented:
My Reports Server is Repsrv@sec12-pc
0
 
Helena Markováprogrammer-analystCommented:
You can create an user defined trigger in a Form and call this trigger from a Menu (by using EXECUTE_TRIGGER built-in).
In my code:
L_REPORT_SERVER - it is the name of the report server - Repsrv@sec12-pc
L_REPORT_SERVER_PATH - e.g. http://sba.jss.sk:7779
 or if you want to run the report from Form Developer it is something similar to http://the_name of yourpc:8888/reports'
0
 
Helena Markováprogrammer-analystCommented:
I am calling my report from a Menu so how do I get the find_report_object() - you can create a dummy report in the Form (node Reports)

    report_id:= FIND_REPORT_OBJECT(dummy_report);
    SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,L_REPORT); -- L_REPORT is the name of the real report

0
 
bsippyAuthor Commented:
How can I solve he following error
FRM-41213:Unable to connect to the Report server Repsrv@sec12-pc.
Thnx in advance
0
 
Helena Markováprogrammer-analystCommented:
... Ensure that Reports Server being referenced in the RUN_REPORT_OBJECT code is started. Note, the in-process Report Server may not be started if no reports requests have been run prior to calling a report from the forms application. It is necessary to first invoke the rwservlet for the in-process server to be started ...
0
 
bsippyAuthor Commented:
The Report Server is started.

The code I have used is as below

Declare
pl_id paramlist;
pl_name VARCHAR2(10):='tempdata';

report_id REPORT_OBJECT;
ReportServerJob VARCHAR2(100);
rep_status VARCHAR2(200);
L_REPORT_SERVER VARCHAR2(30):='Repsrv@sec12-pc';
L_REPORT_SERVER_PATH VARCHAR2(30);
L_REPORT_DESFORMAT VARCHAR2(15):='pdf';
report_job_id VARCHAR2(200);

Begin

if :global.plr is null then
message('Cannot run report');
raise form_trigger_failure;
end if;

pl_id := Get_Parameter_List('p_list');
if not Id_Null(pl_id) then
Destroy_Parameter_List(pl_id);
end if;

pl_id := create_parameter_list('p_list');
add_parameter(pl_id,'hlr_num',text_parameter,:global.plr);


report_id := find_report_object('TEST');
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_OTHER,'TEST.PLR='||:global.hlr||'paramform=no');

SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,'C:\test.rdf');
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,BATCH);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,FILE);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,L_REPORT_DESFORMAT);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,L_REPORT_SERVER);

report_job_id := RUN_REPORT_OBJECT(report_id,pl_id);
-- report_job_id := RUN_REPORT_OBJECT(report_id);
rep_status:= REPORT_OBJECT_STATUS(report_job_id);

WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')
LOOP
rep_status := run_report_object(report_id);
END LOOP;
IF rep_status='FINISHED' THEN
WEB.SHOW_DOCUMENT(L_REPORT_SERVER_PATH||'/reports/rwservlet/getjobid'||substr(report_job_id,instr(report_job_id,'_'-1)+1)||'?'||'server='||L_REPORT_SERVER,'_blank');
ELSE
MESSAGE('Error');
END IF;
End;

but when i run my form and try running the report i get the error

frm-41213: Unable to connect to report server Repsrv@sec12-pc

this is VERY URGENT pls help.

Thanx in advance.
0
 
bsippyAuthor Commented:
Oops I didn't set L_REPORT_SERVER_PATH...as I set it..there is no error but the report isn't opening up....any ideas

Thanx in advance
0
 
Helena Markováprogrammer-analystCommented:
I think that the problem can be caused by L_REPORT_DESFORMAT VARCHAR2(15):='pdf';
Please, try it with
L_REPORT_DESFORMAT VARCHAR2(15):='HTML';
0
 
bsippyAuthor Commented:
It is still giving me the FRM-41213 error......is there any configuration setting I am missing?
0
 
Helena Markováprogrammer-analystCommented:
Can the report be run from a simple form ? or from Developer (not iAS) ?
On this site (free registration) - http://www.oracle.com/technology/products/forms/techlisting10g.html - you can read Integration part ...
0
 
bsippyAuthor Commented:
I can run the report directly from the browser
http://<server>:<port>/reports/rwservlet?report=<report>&.... <other parameters>
0
 
Helena Markováprogrammer-analystCommented:
I am rather confused by your answer:
"Oops I didn't set L_REPORT_SERVER_PATH...as I set it..there is no error but the report isn't opening up....any ideas"
So if it has been 'pdf' there is no error but it is blank.

You can try this:
1) in regedit: rwserver -install repserver90 autostart=yes (repserver90 or another is file repserver90.dat located in <<oracle_home>>\reports\server\)
2) start OC4J
3) run report from Forms
0
 
bsippyAuthor Commented:
Hi,

Thank you...but I keep on getting the error FRM-41213:Unable to connect to the Report server Repsrv@sec12-pc...so I used
Web.show_document('/reports/rwservlet?server=Repsrv@sec12-pc&report=C:/myApp/test.rdf&desformat=pdf&destype=CACHE&userid=user/pwd@database&paramform=no&tlr_num=3171','_blank');

This worked but the draw back is the username and pwd is visible on the address.

0
 
Helena Markováprogrammer-analystCommented:
Maybe there is problem with authentication there.
You can try this solution from MetaLink:
The sqlnet.ora files contain the following declaration: SQLNET.AUTHENTICATION_SERVICES=(NTS)  in both Database ORACLE_HOME/network/admin And 9iAS ORACLE_HOME/network/admin
Workaround:
Comment out the SQLNET.AUTHENTICATION_SERVICES declaration in both sqlnet.ora files.  Stop the database, database listnener and http listener and restart them.
0
 
Helena Markováprogrammer-analystCommented:
Please, check this:
1) Set the FORMS90_PATH so that it reflects the location of the Forms 10g executables.
2) Set the CLASSPATH (*.env file) including zrclient.jar and rwrun.jar

similar to

CLASSPATH=E:\Ora10gAS\j2ee\OC4J_BI_Forms\applications\forms90app\forms90web\WEB-INF\lib\f90srv.jar;E:\Ora10gAS\jlib\repository.jar;E:\Ora10gAS\jlib\ldapjclnt9.jar;E:\Ora10gAS\jlib\debugger.jar;E:\Ora10gAS\jlib\ewt3.jar;E:\Ora10gAS\jlib\share.jar;E:\Ora10gAS\jlib\utj90.jar;E:\Ora10gAS\jlib\zrclient.jar;E:\Ora10gAS\reports\jlib\rwrun.jar

0
 
bsippyAuthor Commented:
Thank you,
I will give it  a try
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 10
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now