How to integrate BI Publisher in Oracle Forms – Part I

In the next couple of blog entries I would like to give a step by step introduction on how to integrate Oracle’s offspring BI Publisher into Oracle Forms. Since the latest release of Oracle Forms (12.2.1.3.0) will be the last release with Oracle Reports there is the neccessary to move to another technology. Maybe in further posts I will also explain other possibilities like Jasper Reports etc.
I assume that you’ve installed Oracle Forms and the BI Publisher in the version 12.2.1.3.0 and will not explain how they need to be installed. For the demo I will use Scott/tiger to get some data into Oracle Forms and the BI Publisher.

Datasource & Datamodell

First we will create a Demo report in the BI Publisher. To do so login to the BI console by opening following URL in your Browser:

http://<Servername>:<Port>/xmlpserver

Add your database by clicking on “Administration”>”JDBC-Connection”:

Unbenannt

Click on “Add Data Source” and configure your datasource based on your installation.

Unbenannt2.PNG

Next we need to create a new datamodel for the report that we want to call from Forms. To do so we go back to the main window and click on “datamodell”. Here you can create your datamodell directly by adding a new SQL-Query and write your SQL or you can use the SQL builder. I’ve created a new SQL-Query named “Employees_in_department” with the following SQL:

select "DEPT"."DNAME" as "DNAME",
 "EMP"."ENAME" as "ENAME",
 "EMP"."JOB" as "JOB",
 "EMP"."HIREDATE" as "HIREDATE",
 "EMP"."SAL" as "SAL"
 from "SCOTT"."EMP" "EMP",
 "SCOTT"."DEPT" "DEPT" 
 where "DEPT"."DEPTNO"="EMP"."DEPTNO" 
group by "DEPT"."DNAME",
 "EMP"."ENAME",
 "EMP"."JOB",
 "EMP"."HIREDATE",
 "EMP"."SAL"

Make sure to use your new datasource “FormsDemo”. Now we can click on “Data” and produce some demodata, that we can use to see how our report will look like. Select the amount of rows you want to query and click on “Save as sample Data”. Don’t forget to save your datamodell!

Unbenannt3

Create Report & Layout

Now we can create the actual report. For this I will use the online builder rather then the word plugin. Go to the main window and click on “report”. You can either use the wizard or build the report manually. Select your datamodell, click on “Use Report Editior” and “Finish”. You will be asked, where on the server you would like to save the report. Store it in the same folder as your datamodell.
Now you can create a Layout for your report. Select “Chart and Table”. Switch the demo chart to a pie chart and add the employee name column as value and the department name as series.
In the table section add the departmant name, employee name, job, and salary and group the table by the department column by selecting the column and choose “group left”.

Unbenannt4

Click on save. Et voila – you’ve created a demo BI Publisher Report. You can click on “Done” and “View Report” to get a preview of the report.

In the next blog post I will explain how to build a demo form, which executes the report in Forms 12c.

Advertisements

Oracle Forms 12c: Error 801 while compiling

If you’ve already upgrated to the latest Forms version (currently 12.2.1.2) you’ve maybe encountered following problem:
If you use a cursor, that is specified in a package specification within your forms and define a rowtype based on that cursor that is fetched into the rowtype in a different other part of the form (in my case the package body). If you then try to compile you may get the error 801 internal error [unexpected fragile external reference] from figure 1.

unbenannt

Figure 1: Compilation error

Metalink documents

We’ve opened a service request for that problem in metalink and received an answer, that I would like to show you in this blogpost. There is also a bug filed as well: Bug 23250870
The solution for the compilation error and it’s solution is described the following document in Metalink. It says, that the base bug is a PLSQL bug.
Rather than providing a patch for that problem, Oracle gives the information, that you have to set following variable to one to enable the compilation:

FORMS_PLSQL_BHVR_COMMON_SQL=1

If you set a path variable in CMD before starting the Forms Developer and then execute frmbld.exe you are able to compile a module that contains a construct like:

PACKAGE Test IS
  cursor c1 is select name_object from all_obj;
  master_rec            c1%ROWTYPE;
END;
Package body Test is  
  Procedure p1 is
  begin
      fetch c1 into master_rec;      
      close c1;
  end p1;    
end;  

 

Registry entry & environment variable

Warning: Changing the Windows registry can mess up your OS.

I’ve tried to also create a registry entry in a Windows machine under HKEY_LOCAL_MACHINE\Software\Oracle\KEY_OracleHome1 and set it to “1” so you don’t have to execute a batch before running Forms Builder. Also I have tried to create a new environment variable. Both options enable the Forms Builder to compile. But I had complications while compiling a bigger customer forms module, when I hit directly Ctrl+shift+k – the Forms Builder crashed when I did not connect to the database before hitting the hotkey.

OTN links

https://community.oracle.com/thread/3986558

https://community.oracle.com/message/13371977#13371977

Configure Webstart for custom Jar-Files

The following text is from a knowledgebase entry from my colleagues from the US. It describes how you have to configure Forms & Reports to be able to use PITSS.CON with webstart. Since adding your jar-files to the extensions.jnlp is a general task when you want to use webstart in 12c, I thought that this could also be helpful for people that want to configure webstart for their application in 12c.

Oracle Forms 12c is the first version of Forms to be supported with the new Java Web Start functionality. This allows you to launch your Forms application using any certified browser including Google Chrome. PITSS.CON can also be configured to run with Java Web Start. To implement this functionality with PITSS.CON, you will need to run these steps:

  1. Open up formsweb.cfg located in %DOMAIN_HOME%\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config.
  2. Within your config section(s) for PITSS.CON, add the following parameters inside it:
    1. basejnlp=webutil.jnlp
      webstart=enabled
  3. Save and close the file.
  4. Go to %ORACLE_HOME%\forms\java and open up extensions.jnlp in a text editor (make a backup of this file first).
  5. Look for <!– <jar href=”jacob.jar”/ –>. Uncomment this line. It should look like this: <jar href=”jacob.jar”/>
    1. NOTE: This fixes a Forms startup issue when using Java Web Start. Source: Oracle Support note 2083540.1
  6. Add the following lines for each of the PITSS.CON jar files (this will allow all jar files for PITSS.CON to be used while running Java Web Start):
    1. <jar href=”/forms/PitssJava/pitssicon.jar”/>
      <jar href=”/forms/PitssJava/pitssE.jar”/>
      <jar href=”/forms/PitssJava/pitssH.jar”/>
      <jar href=”/forms/PitssJava/pitssFS.jar”/>
      <jar href=”/forms/PitssJava/pitssCFS.jar”/>
      <jar href=”/forms/PitssJava/pitssLE.jar”/>
      <jar href=”/forms/PitssJava/FileLastModified.jar”/>
      <jar href=”/forms/PitssJava/pitss_calendar.jar”/>
      <jar href=”/forms/PitssJava/pitssXSL.jar”/>
      <jar href=”/forms/PitssJava/classes12.jar”/>
      <jar href=”/forms/PitssJava/PitssFileUtils.jar”/>
      <jar href=”/forms/PitssJava/BarChart.jar”/>
      <jar href=”/forms/PitssJava/colorpicker.jar”/>
      <jar href=”/forms/PitssJava/jcommon-1.0.16.jar”/>
      <jar href=”/forms/PitssJava/jfreechart.jar”/>
      <jar href=”/forms/PitssJava/PieChart.jar”/>
      <jar href=”/forms/PitssJava/OGD.jar”/>
    2. The file should look like this:
    3. Java Web Start PITSSCON
  7. Save and close the file.
  8. Clear your Java cache on your PC where you plan to launch PITSS.CON.
  9. Launch PITSS.CON.

After completing the steps above, you should be able to use PITSS.CON using Java Web Start.

über Oracle Knowledgebase – PITSS America, LLC

Running Forms 12c with SSL and Java Web Start Uses HTTP Instead of HTTPS

There is a known SSL-related issue when you run Oracle Forms 12c using the Java Web Start functionality. What happens is when you launch your Forms application using Java Web Start using HTTPS, you will notice that the URL will change to using HTTP instead of HTTPS. In some cases, you may encounter the following error:

“General Exception”

“ExitException: Unable to load resource: http://<host&gt;:<HTTPS_PORT>/forms/java/extensions.jnlp”

HTTPS Java Web Start issue

HTTPS Java Web Start issue

To fix this problem, you will need to add the following parameter inside your config section in formsweb.cfg which is configured for Java Web Start:

webstart_codebase=http://<host>:<HTTP_PORT>/forms/java

HTTPS Java Web Start fix

After making this change, the Java Web Start session should maintain the SSL connection using HTTPS without this error appearing.

thanks to my colleagues from PITSS LLC via Oracle Knowledgebase – PITSS America, LLC

ADF: Handling deletion in TreeTables

For the current project that I am working for, we had the requirement to display information in a table that should consist of Master and Detail rows. I knew that there is a component in ADF to solve this requirement quite easily: af:TreeTable.

af:treeTable

So I added my master as a TreeTable component and added the detail as a new level. Under display attributes you can select the attributes that shall be displayed.

Unbenannt

After adding the af:treeTable component you have to adjust the components in your page –

Unbenannt

for example will the added attributes be displayed in one af:outputText component in one node. To change that add new columns to the tree table by performing a rightclick on it -> insert inside Tree Table -> column. You can choose the values by using writing for example:

value=”#{node.Name}”

Hint: In Jdeveloper version 12.1.3 I faced the issue that there was only an autosuggest for the Master attributes. Just type your detail attributes likewise, seems like this is a bug.

Deleting selected row

When it came to deleting rows I had the same problem as probably  many more developers before me: I thought that I can just use the standard delete operation from the bindings. Well that is just working for the master. So I did some research and found following blog entry:
http://www.jobinesh.com/2010/05/crud-operations-on-tree-table.html

The author discribes how to implement methods in the Bean to extract the RowIterator and the key of the af:TreeTable. With that you can implement a method in the model to delete the any selected row and add it as a client implementation.

After that I asked myself, if that was all or if I would have to implement some more. You have to modify your association to be able to delete Master records and cascade the deletion to the details. For that, open the association and click on relationship. Under behavior you find the option composite association & implement cascade delete. When you select this option, the deletion of a master row will result in deleting all child records.

Unbenannt

 

 

 

 

 

 

 

Update: jp2launcher.exe workaround

So, first post in the year 2016 – its gonna be a short one 🙂

In a previous post in this blog I mentioned a problem for using the webstart in Oracle Forms 11g: That the jp2launcher.exe process will not be terminated automatically when the webstart window is closed (view post). I ran a few tests the last days and it seems, that this problem is solved with the new release of Forms 12c as the webstart is included as a separated configuration option.

So I just wanted to let you know, that if you plan to use the webstart feature and to upgrade to 12c the mentioned solution is obsolete.

Start options in Oracle Forms 12c

So I’ve read quite a lot of blog entries and news regarding Oracle Forms 12c in the last weeks since the release. It is nice to hear and see that Oracle Forms still is not dead.
The release brought some nice features with it. Today I would like to have a look at the different start options, that are now available.

Embedded Java applet

The standard start in the last couple of years. So its still possible to call your form application through the Java plugin embedded in html. Just create a new section in the formsweb.cfg. The parameters for the standard behaviour are:

baseHTML=webutilbase.htm
baseHTMLjpi=webutiljpi.htm

Unbenannt

There is also an option to embed JNLP in html:

basejnlp=base.jnlp
baseHTMLjpi=basejpi_jnlp.htm

Both options require a Java Plugin and a browser.

Java Webstart

This method of starting a Formsapplication is not new. But whats new is the fact that Oracle now supports this option. Since more and more browsers stopped or will stop supporting the NPAPI-Plugin this step was neccessary. Google Chrome already stopped the support, Firefox will support the plugin until December 2016. Microsoft still supports it in the Internet Explorer 11, while Edge does not.
So if you want to use webstart, just copy following lines into your configuration section:

webstart=enabled
basejnlp=base.jnlp

Afterwards you can open your application through entering following URL:

http://<host>:port/forms/frmservlet?config=<configname>

You will be asked to open a file (frmservlet). Just open it and you should get the following screen – you need to have a JRE installed for that.

2

There are some facts that have to be considered when using the webstart (copied from Michael Ferrante’s PPT):

  •  Limited SSO support (requires
    browser). SSO cookie will not be shared
    with browsers launched from Forms.
  •  No support for SSO Logout or Java
    Script Integration.
  •  Requires either JDK or Java Plugin (JRE).
  •  Browser optional (required with SSO).
  •  Presents application with native
    appearance.

Standalone

There is a new standalone option. For that you have to download the Oracle Forms Stand-alone Luncher(FSAL). A howto is to be found on your server environment under:

http://<host>:<port>/forms/html/fsal.htm

I did not have the time to fully test this option but here is what Michael Farrante stated in his Oracle Open World 2015 presentation:

  • No support for SSO, SSO Logout or Java
    Script Integration.
  • Requires either JDK or Java Plugin (JRE)
    installation.
  • Browser not required.
  • Presents application with native
    appearance.
  • Example page available at:
    /forms/html/fsal.htm

This option shall offer “a browser-less, more client/server-like interface”.

Oracle Forms 12c & BI Publisher

Today I started to have a look at the possibilities of integrating the BI Publisher in Forms 12c.

2

Sadly I didn’t find anything regarding this topic in the online documentation – so I created a report object and had a look at the properties.

We found out, that there are several new properties in the report object. So I created a forms module with a simple button and tried to call a BI-Publisher Server. As expected, my first try was not successful – I got an “FRM-41223: BI Publisher integration error”. So I had a look at the online help under “SET_REPORT_OBJECT_PROPERTY” to see if there are any new properties – and yes there are quite a lot new BI-Properties:

  • BIP_SSL_CONNECTION: Indicates whether or not SSL is mandatory or optional.
  • BIP_SERVICE_LOCATION: The URL to the Oracle BI_Publisher server Web Services WSDL for ScheduleServices.
  • BIP_REPORT_PATH: The absolute path to the report to be run (on the BI_Publisher server).
  • BIP_REPORT_LOCALE: Preferred report locale.
  • BIP_REPORT_FORMAT: Desired report output format.
  • BIP_REPORT_TEMPLATE: A template registered with the report specified in BIP_REPORT_PATH. It is common practice to provide this property along with report name/path but BI-Publisher gives the option of setting a default value when creating/editing a report, therefore this is an optional property in Forms. Value is used and validated by BI-Publisher.
  • BIP_REPORT_PARAMETERS: A list of name/value pairs separated by a space.
  • BIP_USER: The BI-Publisher username required to access the Web Service. This value cannot be set in the Property Palette.
  • BIP_PASSWORD: The BI-Publisher user password required to access the Web Service. This value cannot be set in the Property Palette.
  • FTP_SERVER: FTP server name as configured in BI-Publisher.
  • FTP_SECURED: Determine if FTP will be secured (i.e. SFTP) or unsecure.
  • FTP_USER: FTP server username.
  • FTP_PASSWORD: FTP server user password.
  • FTP_FILE_NAME: Output file name to be stored on FTP server.
  • FAX_NUMBER: Fax number. Format as required by your server configuration.
  • FAX_SERVER: Name of Fax server as configured in BI-Publisher.
  • MAIL_TO: The mailto email address.
  • MAIL_FROM: The mailfrom email address.
  • MAIL_CC: The mailcc email address.
  • MAIL_BCC: The mailbcc email address.
  • MAIL_REPLYTO: The replyto email address.
  • MAIL_SUBJECT: The email subject string.
  • MAIL_BODY: The email body string.
  • MAIL_SERVER: Name of the email server (SMTP) configured in BI-Publsher.
  • LOCAL_FILE_NAME: Absolute path and filename of the output file.
  • PRINT_COPIES: Number of copies to print.
  • PRINT_ORIENTATION: Page orientation.
  • PRINT_RANGE: Page range to print.
  • PRINT_SIDE: Enable two-sided printing. This setting will be ignored if the target printer does not support two-sided printing
  • PRINT_TRAY: The tray number. If you do not specify this option, the default media of the target printer will be used. It will be ignored if the target printer doesn’t support the media option.
  • PRINTER_NAME: Printer name on the BI-Publisher host to send the documents to.
  • WEBDAV_AUTH_TYPE: The authentication type.
  • WEBDAV_USER: The WebDAV username.
  • WEBDAV_PASSWORD: The WebDAV user password.
  • WEBDAV_FILE_NAME: The remote filename.
  • WEBDAV_SERVER: The WebDAV server name.

After setting the properties BIP_USER & BIP_PASSWORD I was able to run the BI-Report:3