Oracle Fusion Middleware 12c
Oracle Fusion Middleware 12cIntegrating Oracle Reports with Oracle FormsORACLE WHITE PAPER MAY 2016
Table of ContentsIntroduction2Oracle Forms2Oracle Reports3The Oracle Forms RUN REPORT OBJECT Built-in3How to use RUN REPORT OBJECT4RUN REPORT OBJECT Examples4Using a Parameter List with RUN REPORT OBJECT7Calling Reports that Display a Parameter Form8Solving the Problem with “PFACTION”8Using PL/SQL Functions to Encode URL Parameters9Building a Procedure to Call Reports with Parameter Forms10Examples of How to Call the Generic ProcedureThe Oracle Forms WEB.SHOW DOCUMENT Built-in1314WEB.SHOW DOCUMENT Syntax14Calling Reports using WEB.SHOW DOCUMENT15Hiding the Username and Password16Conclusion1 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C17
IntroductionThis paper discusses how to integrate Oracle Reports with Oracle Forms. After reading thiswhitepaper you will:» Understand how to use the Oracle Forms RUN REPORT OBJECT built-in.» Understand how a report is requested from an Oracle Forms application.» Understand how to retrieve a completed report from Oracle Forms.» Understand how to call reports which include a Reports parameter form.» Understand how to call Oracle Reports from Oracle Forms when single sign-on (SSO) is enabled.This document is intended for individuals with a working knowledge of how to write Oracle Formsapplication code and understand the basic functionality in Oracle Forms and Reports on the middletier. Some understanding of HTTP Server and WebLogic Server functionality will also be helpful. Thesample code found within this document is provided for illustration and educational purposes only.This code should not be used in production applications without first performing thorough andcomplete testing prior to use.Oracle FormsOracle Forms consists of two high level components: the Oracle Forms Developer design-time component (aka theForm Builder) and Oracle Fusion Middleware – Forms Services deployment (aka Forms Runtime) component. Forthe purpose of this document, we will only be discussing those features and built-ins that are necessary to call areport from an Oracle Forms application.There are two Oracle Forms built-ins which are supported for calling Oracle Reports from Oracle Forms:» RUN REPORT OBJECT» WEB.SHOW DOCUMENTThese built-ins are explained in more detail within the Oracle Forms Builder Online help. An explanation of howthese will be used to call Oracle Reports will be explained later in this paper. More information about deployingOracle Forms can be found in the “Oracle Forms Deployment Guide”, which is included in the Fusion Middleware12c documentation library on the Oracle Technology Network (OTN).If your application is being migrated from an earlier version of Oracle Forms, specifically version 6.x or older and thebuilt-in RUN PRODUCT was used for Oracle Forms and Oracle Reports integration and you are not able or willingto rewrite your code to use RUN REPORT OBJECT, please refer to the documentation which discusses how touse the Forms Migration Assistant (FMA). This information can be found in the Fusion Middleware documentationlibrary in the document titled, “Oracle Forms Upgrading Oracle Forms 6i to Oracle Forms 12c”.2 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
Oracle ReportsLike Oracle Forms, Oracle Reports consists of a primary design-time tool commonly referred to as the OracleReports Builder and the Oracle Fusion Middleware – Reports Server component for deployment. The deploymentcomponent within Fusion Middleware is referred to as Oracle Reports Services or Server. Throughout this paper,the terms Reports Services and Reports Server are used interchangeably for the same component(s).More information about deploying Oracle Reports can be found in the Oracle Reports deployment guide, titled “Publishing Reports with Oracle ReportsServices” which is included in the Fusion Middleware 12c documentation library on OTN.The Oracle Forms RUN REPORT OBJECT Built-inThe most secure approach for calling Oracle Reports from Oracle Forms is to use the RUN REPORT OBJECTbuilt-in. Because the user’s database connection is implicitly passed from Oracle Forms to Oracle Reports on themiddle tier server, there is no risk of interception as when passed such information in a URL.Before Oracle Forms can make calls to Oracle Reports, it will be necessary to set a new environment variable in theForms environment settings file (e.g. default.env). Set COMPONENT CONFIG PATH to the fully qualified path ofthe Reports Tools Component. For example:COMPONENT CONFIG PATH sComponent/ reports tools component name In Oracle Forms Builder, to use the RUN REPORT OBJECT built-in, you will need to create a new Reports objectunder the “Reports” node in the Object Navigator. Each Reports object has a logical name, which is used withinForms to call the report from PL/SQL. You can optionally create a new Reports object for each physical Reports file.One Reports object can also be used with many physical Reports files. The attributes of this object can be set in theBuilder’s Property Palette at design-time or can be set programmatically at runtime.Figure 1: Oracle Forms Object Navigator and Property Palette. Note that the “Reports” node includes the objects “MYREPORT1”, “REPTEST”, and“RP2RRO”. The physical Oracle Reports file referenced by the “MYREPORT1” object is defined as “reptest.rdf”. The Oracle Reports runtime settingsbelow the “Reports” node in the Property Palette can be overwritten at runtime using SET REPORT OBJECT PROPERTY.3 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
How to use RUN REPORT OBJECTTo access a remote Reports Server using RUN REPORT OBJECT, Oracle Reports Services must be accessiblefor the Report object in Oracle Forms. You can do this dynamically, using theSET REPORT OBJECT PROPERTY built-in, or statically, by entering the Oracle Reports Server name string intothe Property Palette of the Report object.It is also important to note that Oracle Forms Services and Oracle Reports Services must reside within the samenetwork subnet in order to work properly. If they are not, either the Oracle Reports Naming Service or OracleReports Bridge can be used to overcome this particular configuration limitation. Refer to the “Publishing Reportswith Oracle Reports Services” document previously mentioned for more information about using the Reports NamingService or a Bridge.RUN REPORT OBJECT ExamplesExample 1The following example runs a report using the Oracle Forms built-in RUN REPORT OBJECT. Note that at thispoint we are only requesting that a report be run. The data retrieved (i.e. report output) will not be returned to theend-user at this point. This may be desirable in some cases. If so, set the DESTYPE to “FILE” in order topermanently store the file on the server for later use.In this example, the Reports object name is “MyReport1”. A user defined Reports parameter, “p deptno”, is passedusing the value of the “dept.deptno” field. The parameter form is suppressed using “paramform no”.DECLAREreport id Report Object;ReportServerJob VARCHAR2(254);BEGINreport id : find report object(‘MyReport1’);SET REPORT OBJECT PROPERTY(report id,REPORT COMM MODE,SYNCHRONOUS);SET REPORT OBJECT PROPERTY(report id,REPORT DESTYPE,CACHE);SET REPORT OBJECT PROPERTY(report id, REPORT DESFORMAT, ‘PDF’);SET REPORT OBJECT PROPERTY(report id,REPORT SERVER,’Repsrv’);SET REPORT OBJECT PROPERTY(report id,REPORT OTHER,’p deptno ‘ :Dept.Deptno ’ paramform no’);ReportServerJob : run report object(report id);END;Figure 2: General use of RUN REPORT OBJECTExample 2The following example uses a synchronous call to RUN REPORT OBJECT to run a report. It expects the Reportsobject name, the Reports Server name, and the desired output format (PDF, HTML, HTMLCSS, etc) to be passedas parameters. It will also attempt to verify that the report was successfully generated, and then display the resultsto the end user in a browser. The use of a procedure such as this is recommended in cases where the application islikely to call out to Reports from various places within the application.4 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
PROCEDURE RUN REPORT OBJECT PROC (vc reportoj Varchar2, vc reportserver varchar2, vc runformat varchar2) ISv report id Report Object;vc ReportServerJob VARCHAR2(100); /* unique id for each Report request */vc rep status VARCHAR2(100); /* status of the Report job */vjob id VARCHAR2(100); /* job id as number only string*/BEGIN/* Get a handle to the Report Object */v report id: FIND REPORT OBJECT(vc reportoj);/* Define the report output format and the name of the Reports Server as well as a user-defined parameter.Pass the department number from Forms to Reports. There is no need for a parameter form to be displayed,so paramform is set to “no”.*/SET REPORT OBJECT PROPERTY(v report id,REPORT DESFORMAT,vc runformat);SET REPORT OBJECT PROPERTY(v report id,REPORT DESTYPE,CACHE);SET REPORT OBJECT PROPERTY(v report id,REPORT COMM MODE,SYNCHRONOUS);SET REPORT OBJECT PROPERTY(v report id,REPORT SERVER,vc reportserver);SET REPORT OBJECT PROPERTY(v report id,REPORT OTHER,’p deptno ‘ :dept.deptno ’paramform no’);vc ReportServerJob: RUN REPORT OBJECT(v report id);vjob id : substr(vc ReportServerJob,instr(vc ReportServerJob,’ ’,-1) 1);/* Check the report status. Because this was a synchronous call ( REPORT COMM MODE),the status check will only return FINSIHED or an error. If COMM MODE is set to “asynchronous”, a timershould be used to periodically change the status of the running report before attempting to display it. */vc rep status : REPORT OBJECT STATUS(vc ReportServerJob);IF vc rep status ‘FINISHED’ THEN/* Call the Reports output to be displayed in the browser. The URL for relative addressing is validonly when the Reports Server resides on the same host as the Forms Server and is accessed via the same port.For accessing a remote Reports environment, you must use a fully qualified URL (i.e. http://hostname:port ) */WEB.SHOW DOCUMENT (‘/reports/rwservlet/getjobid’ vjob id ’?server ’ vc reportserver,’ blank’);ELSEmessage (‘Report failed with error message ‘ vc rep status);END IF;END;Figure 3: Using RUN REPORT OBJECT for integrating calls to Oracle Reports5 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
If you are upgrading from Oracle Forms or Oracle Reports 6i, when calling WEB.SHOW DOCUMENT you will needto modify the Reports job ID that is retrieved by the RUN REPORT OBJECT built-in so that the Reports Servername is not included.To use the procedure described above, you would pass the following information in a “When-Button-Pressed” triggeror other appropriate trigger:RUN REPORT OBJECT PROC ( ‘REPORT OBJECT’’ , ‘REPORT SERVER NAME’ ‘, ‘FORMAT’ )REPORT OBJECTForms Report object name containing the rdf filename for the ReportREPORT SERVER NAMEName of the Reports ServerFORMATAny of these formats: html html css pdf xml delimited rtfFigure 4: Parameters needed to use RUN REPORT OBJECT PROCA synchronous call to Reports will cause the user to wait while the report is processed on the server.For long-running Reports, it is best that the report be run asynchronously by setting the REPORT COMM MODEproperty to asynchronous and the REPORT EXECUTION MODE to batch. For example:SET REPORT OBJECT PROPERTY(report id,REPORT EXECUTION MODE,BATCH);SET REPORT OBJECT PROPERTY(report id,REPORT COMM MODE,ASYNCHRONOUS);After calling RUN REPORT OBJECT, you must create a timer to run periodic checks on the currentREPORT OBJECT STATUS in a When-Timer-Expired trigger. After the report is generated, the “When-TimerExpired” trigger calls the WEB.SHOW DOCUMENT built-in to display the Reports output file, identified by its uniquejob ID, to the client’s browser.Here is an example of how the report status can be checked from the When-Timer-Expired trigger:(.)/* :global.vc ReportServerJob needs to be global because the information about the Report job id is sharedbetween the trigger code that starts the report and the trigger code When-Timer-Expired that checks the Report status. */vc rep status: REPORT OBJECT STATUS(:global.vc ReportServerJob);IF vc rep status ’FINISHED’ THENvjob id : substr(:global.vc ReportServerJob,length(reportserver) 2,length(:global.vc ReportServerJob));WEB.SHOW DOCUMENT (‘/reports/rwservlet/getjobid’ :vjob id '?server ’ vc reportserver,' blank');DELETE TIMER (timer id); -- Report done. No need to check any more.ELSIF vc rep status not in (‘RUNNING’,’OPENING REPORT’,’ENQUEUED’) THENmessage (vc rep status ’ Report output aborted’);DELETE TIMER (timer id); -- Report failed. No need to check any more.END IF;(.)Figure 5: Performing asynchronous call to Reports and checking its status from When-Timer-Expired6 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
Using a Parameter List with RUN REPORT OBJECTWith the RUN PRODUCT1 built-in (no longer supported for use in Oracle Forms), Reports system parameters anduser-defined parameters are passed in a parameter list. The same parameter lists can be used withRUN REPORT OBJECT, with the exception of the system parameters, which need to be set with theSET REPORT OBJECT PROPERTY built-in.2REPORT EXECUTION MODEBATCH or RUNTIMEREPORT COMM MODESYNCHRONOUS ASYNCHRONOUSREPORT DESTYPEFILE PRINTER MAIL CACHEREPORT DESFORMATHTML HTMLCSS PDF RTF XML DELIMITED SPREADSHEETREPORT FILENAMEThe report filenameREPORT DESNAMEThe report destination nameREPORT SERVERThe Report Server name33Figure 6: List of system parameters used by RUN REPORT OBJECT.If your existing parameter list already contains definitions for system parameters, you may experience errors. Toprevent such problems from occurring, modify the parameter list itself, either by removing the entries for DESNAMEand DESTYPE, or by addingDELETE PARAMETER ( parameter list ,’ name ‘);to your code before using SET REPORT OBJECT PROPERTY. The syntax for using parameter lists inRUN REPORT OBJECT is as follows:ReportServerJob : RUN REPORT OBJECT (report id,paramlist id);Note that having DESTYPE defined both in the parameter list and in SET REPORT OBJECT PROPERTIESshould not prevent the module from compiling, but may prevent it from running.1. Using RUN PRODUCT to generate Reports output is not supported in Oracle Forms 9.0.4 and greater. Forms module containing integrated calls toReports using RUN PRODUCT will not compile. Refer to the Forms Upgrade Guide for information on how to use the Forms Migration Assistance(FMA) or consider updating your code as discussed in this paper.2. Report Execution Mode is a client/ server feature and no longer used in Oracle Forms. However, setting the value to either BATCH or RUNTIME isrequired.3. Additional DESTYPE and DESFORMAT values can be found in the Oracle Reports deployment guide, titled “Publishing Reports with Oracle ReportsServices”7 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
Calling Reports that Display a Parameter FormUsing the previous examples, a report’s parameter form will not work when called from RUN REPORT OBJECTbecause the ACTION attribute in the generated report HTML parameter form is empty. RUN REPORT OBJECTcalls are sent directly to Oracle Reports on the server. Therefore, Oracle Reports cannot access the webenvironment to obtain the information required to populate the action attribute when generating the HTML parameterform.The ACTION attribute is part of the standard HTML FORM tag that defines what to do when a user presses thesubmit button. The ACTION attribute in the Oracle Reports parameter form should contain hidden runtimeparameters that are required to process the request after the user presses the submit button. Additional code isrequired to overcome this condition.Solving the Problem with “PFACTION”“PFACTION” is a command line parameter in Oracle Reports that can be used to add the hidden runtime parameterto a report’s parameter form when calling Oracle Reports from Oracle Forms, using RUN REPORT OBJECT. Thesyntax for the value “pfaction” parameter looks like this: request URL to rwservlet ? hidden encoded original url query string The “request URL to rwservlet” portion contains the protocol, the host name, the port, the Oracle Reports webcontext path and the Oracle Reports Servlet name. For etThe “encoded original url query string” portion is a duplicate of all Oracle Reports system and applicationparameters passed from Oracle Forms to Oracle Reports using SET REPORT OBJECT PROPERTY, encoded ina URL. For example, these Reports command line parameters,destype cache desformat htmlcss userid scott/tiger@orclwould be added as a value to the “pfaction” parameter like this,destype cache%20desformat htmlcss%20userid scott%2Ftiger%40orclIn order to call a report containing a parameter form using RUN REPORT OBJECT, do the following:1. Provide the protocol, the host name of the server, and the server port2. Provide the virtual path and name of the Oracle Reports Servlet3. URL encode the parameter value of the “pfaction” command parameterThe following Forms built-in will be used to pass the “pfaction” command from Oracle Forms Services to Reports:SET REPORT OBJECT PROPERTY (rep id,REPORT OTHER, ’pfaction .’);Note that if you are using the REPORT OTHER built-in to pass application parameters to Oracle Reports, theapplication parameters must also be contained in the pfaction parameter.8 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
Using PL/SQL Functions to Encode URL ParametersBecause the “pfaction” parameter is added as an ACTION parameter to the Oracle Reports HTML parameter form, itis important to ensure that no un-encoded characters are included or errors may result. One example that maycause problems when embedded in a URL is a blank (space). Therefore, most developers URL-encode blank as“%20”. The PL/SQL function “ENCODE”, as listed below, is an example that encodes the following characters: “;”, “/”, ”?”, ”:”, ”@”, ” ”, ” ”, ”,” and “ “ (semicolon, forward slash, question mark, colon, at, plus sign, dollar sign, comma,and blank space).FUNCTION ENCODE (URL PARAMS IN Varchar2) RETURN VARCHAR2 ISv urlVARCHAR2(2000) : URL PARAMS IN; -- Url stringv url tempVARCHAR2(4000) : ‘‘; -- Temp URL stringv aVARCHAR2(10); -- conversion variablev bVARCHAR2(10); -- conversion variablecCHAR;iNUMBER(10);BEGINFOR i IN 1.LENGTH(v url) LOOPc: substr(v url,i,1);IF c in (‘;’, ‘/’,’?’,’:’,’@’,’ ’,’ ’,’,’,’ ‘) THENv a : ltrim(to char(trunc(ascii(substr(v url,i,1))/16)));IF v a ‘10’ THEN v a : ‘A’;ELSIF v a ‘11’ THEN v a : ‘B’;ELSIF v a ‘12’ THEN v a : ‘C’;ELSIF v a ‘13’ THEN v a : ‘D’;ELSIF v a ‘14’ THEN v a : ‘E’;ELSIF v a ‘15’ THEN v a : ‘F’;END IF;v b : ltrim(to char(mod(ascii(substr(v url,i,1)),16)));IF v b ‘10’ THEN v b : ‘A’;ELSIF v b ‘11’ THEN v b : ‘B’;ELSIF v b ‘12’ THEN v b : ‘C’;ELSIF v b ‘13’ THEN v b : ‘D’;ELSIF v b ‘14’ THEN v b : ‘E’;ELSIF v b ‘15’ THEN v b : ‘F’;END IF;v url temp : v url temp ’%’ v a v b;ELSEv url temp : v url temp c;END IF;END LOOP;return v url temp;END;Figure 7: PL/SQL function to URL-encode strings9 INTEGRATING ORACLE REPORTS 12C WITH ORACLE FORMS 12C
Building a Procedure to Call Reports with Parameter FormsCalling Oracle Reports using RUN REPORT OBJECT is best handled by a generic PL/SQL procedure in Forms.To successfully call a report from Oracle Forms, the following minimum information needs to be passed to theReports pfaction command:» Desformat, to determine the Reports output format» Destype, to determine the output device, (printer or cache)» Userid, to connect Reports to the database» Reports file name, to specify the Reports module to be executed» Paramform yes, to enable the Reports parameter form to be shown in the client browserThe generic PL/SQL procedure handling
» Understand how to call Oracle Reports from Oracle Forms when single sign-on (SSO) is enabled. This document is intended for individuals with a working knowledge of how to write Oracle Forms application code and understand the basic functionality in Oracle Forms and Reports on the middle tier.
Oracle SOA Suite 12c Oracle Cloud Control 12c Oracle OSB 12c y Consulting Architecture Analysis and Development Testing and Production Support Infrastructure and Tuning Application Maintenance Technology Oracle BPM 12c Oracle SOA 12c OAG 12c OER 12c Oracle Virtual Directory Oracle Identity Manager
1 Introduction to Oracle Fusion Middleware Disaster Recovery 1.1 Overview of Oracle Fusion Middleware Disaster Recovery 1-1 1.1.1 Problem Description and Common Solutions 1-1 1.1.2 Terminology 1-2 1.2 Setting Up Disaster Recovery for Oracle Fusion Middleware Components 1-5 1.2.1 Oracle Fusion Middleware Disaster Recovery Architecture Overview 1-5
OEM 12c Upgrade - Two System (Different Hardware) em.cisco.com. OEM DB. 10g RAC. 10g repository. Targets 10g. Targets 12c. em12c.cisco.com. OEM DB. 11g RAC. 12c repository. Deploy 12c agents. Clone and upgrade repository DB to 11g. Install 12c OMS & upgrade EM repository to 12c. Start 12c OMS & Deferred Data Migration Job. Incremental .
3.7.1 Configuring an OWSM 12c Web Service and an OC4J 10g Client. 3-20 3.7.2 Configuring an OC4J 10g Web Service and an OWSM 12c Client. 3-21 4 Interoperability with Oracle WebLogic Server 12c Web Service Security Environments 4.1 Overview of Interoperability with Oracle WebLogic Server 12c Web
Oracle Database 12c - Disaster recovery solution using Oracle Data Guard and HPE Serviceguard for Linux across production and recovery data centers Oracle Database 12c - High availability solution using Oracle Real Application Clusters (RAC) Oracle Database 12c - Application-consistent Oracle
7 Accessibility Features and Tips for Oracle Forms and Reports . that Oracle has developed for Oracle Fusion Middleware products and services. . Mode in Developing Services with Oracle Service Bus For information about enabling Fusion Middleware Control accessibility mode, see: .
new open source WebLogic Remote Console, enhancements to Kubernetes tooling and support, Oracle Java SE 8 and 11 certification, GraalVM EE certification, and improvements to Oracle Database integration. The next planned release of Oracle Fusion Middleware is Oracle Fusion Middleware 14.1.2,
deploying Oracle Forms applications on the Web. Oracle Fusion Middleware Forms Services delivers out-of-the-box functionality and native services to ensure that Oracle Forms applications automatically scale and perform over any network. Oracle Fusion Middleware Forms Services enables rich, extensible Java clients that are optimized for the Web.File Size: 635KBPage Count: 18
