Displaying Reports in Oracle Forms

Steps you have to perform to display Reports in Oracle Forms.

If your form contains embedded Oracle Reports applications, you can upgrade the form to Oracle Forms by changing the integrated call to Reports 9i to use:

• RUN_REPORT_OBJECT built-in (Do not use the RUN_PRODUCT built-in to call Reports.)

• WEB.SHOW_DOCUMENT built-in

Using RUN_PRODUCT in Oracle Forms to run integrated Reports 9i is no longer supported in this release. Beginning with 12c, the use of this built-in results in a compilation error. The Migration Assistant is provided to help you upgrade your applications to use RUN_REPORT_OBJECT. See About Using the Oracle Forms Migration Assistant.

The following example runs a report using the RUN_REPORT_OBJECT Built-in. The report_object node defined in Oracle Forms is assumed to be "report_node1". A user-defined Reports parameter "p_deptno" is passed by Forms using the value in the "dept.deptno" field. The Reports parameter form is suppressed. Additional details about the logic used in this example is also provided

 DECLARE
 v_report_id 		Report_Object;
 vc_report_job_id  		VARCHAR2(100); 	/* unique id for each Report 	 request */
 vc_rep_status      		VARCHAR2(100);		 /* status of the Report job */
 	  
 BEGIN
 	/* Get a handle to the Report Object itself. */
 	v_report_id:= FIND_REPORT_OBJECT('report_node1');
 	SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_COMM_MODE, SYNCHRONOUS);
 	SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_DESTYPE,CACHE);
 
 /* Define the Report output format and the name of the Reports Server as well as
 a user-defined parameter, passing the department number from Forms to the Report.
 The Reports parameter form is suppressed by setting  paramform  to "no". */
 	SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_DESFORMAT, '<HTML|HTMLCSS|PDF|RTF|XML|DELIMITED>');
 	/* replace <ReportServerTnsName> with the name of the Reports Services as defined
 in your tnsnames.ora file */
 	SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_SERVER, '<ReportServerTnsName>');
 	SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'p_deptno='||:dept.deptno||'paramform=no');
  	/* finally, run the report and retrieve the Reports job_id as a handle to the
 Reports process */
 	vc_report_job_id:=RUN_REPORT_OBJECT(report_id);
 
 /*The report output is not delivered automatically to the client, which is okay
 because the Web is a request model. Thus the next step is to check if the report
 finished. */
 
 	vc_rep_status := REPORT_OBJECT_STATUS(vc_report_job_id);
 	IF vc_rep_status='FINISHED' THEN
 /* Call the Report output to be displayed in a separate browser window. The URL
 for relative addressing is only valid when the Reports Server is on the same host
 as the Forms Server. For accessing a Remote Reports Server on a different
 machine, you must use the prefix http://hostname:port/ */
 web.show_document ('/<virtual path>/<reports cgi or servlet name>/getjobid='||
 vc_report_job_id ||'?server='|| '<ReportServerTnsName>','_blank');
 	ELSE
 	message ('Report failed with error message '||rep_status);
 	END IF;
END;

Example: Additional Details

• Calling a report synchronously makes the user wait while the report gets processed on the server. For long-running Reports, it is recommended that you start the report asynchronously, by setting the REPORT_COMM_MODE property to asynchronous and the REPORT_EXECUTION_ MODE to batch.

  SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,BATCH);

  SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,ASYNCHRONOUS);

• After calling the RUN_REPORT_OBJECT Built-in, you must create a timer to run frequent checks on the current REPORT_OBJECT_STATUS using a When-Timer-Expired trigger. For performance reasons, the timer should not fire more than four times a minute. After the report is generated, the When-Timer-Expired trigger calls the WEB.SHOW_DOCUMENT Built-in to load the Reports output file, identified by a unique job_id, to the client's browser.

  Note:

  Do not forget to delete the timer when it is no longer needed.

  The following example shows the When-Timer-Expired trigger that checks for the Report_Object_Status.

  (...)  
   /* :global.vc_report_job_id needs to be global because the information about
   the Report job_id is shared between the trigger code that starts the Report
   and the When-Trigger-Expired trigger that checks the current Report status. */
   vc_rep_status:= REPORT_OBJECT_STATUS(:global.vc_report_job_id);
   IF vc_rep_status='FINISHED' THEN
   web.show_document ('/<virtual path>/<reports cgi or servlet name>/getjobid='||
   vc_report_job_id ||'?server='|| '<ReportServerTnsName>','_blank');
   ELSIF vc_rep_status not in ('RUNNING','OPENING_REPORT','ENQUEUED') THEN
   	message (vc_rep_status||'  Report output aborted');
    END IF;
  (...)