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

JDeveloper Plugin for Forms2ADF Migration

I’ve migrated my first Oracle Forms module last week. It is the first time for me to be part of a migration project. The colleagues that I am working with instead have a high experience when it comes to ADF and migrating Forms to this technology, while my focus the last couple of years was mostly Oracle Forms. I think that is a possible scenario for many projects, where the aim is to migrate Forms to ADF.

So how can Forms developers start to migrate their application? The first step for me was to gather information on ADF, its architecture and the basics. I tried this with some self training, online materials and the help of my colleagues. When I came to the point to migrate the first module by myself I used the Forms Developer & PITSS.CON to understand how the form worked before and how the workflow was. There are people out there, that tell you that tools won’t do the job for you – and I agree. But they can make your job as a developer much easier. My colleague Mathias Waedt has developed a plugin for JDeveloper, that helps you as a developer when struggling with a migration. It helped me to understand which objects in ADF have been generated and which not. It showed me, were I had to put my effort into. The plugin fetches its information from the PITSS.CON repository.


Unbenannt

When the plugin is started it shows the basic information of the project like the database scheme, the names packages for the different objects (entity objects, view objects, etc.) and some other parameters. With a click on the details tab it is possible to see information of single forms. The form summary tab displays information regarding how many Forms objects already have been transformed to ADF components with PITSS.CON. This gave me a first overview of the upcoming work for that specific module. I saw, that most of the graphical objects were generated and only small modifications  were neccessary here, while I had to modify the business logic that was formerly located in triggers and program units.


Unbenannt2

 

With a click on Form Details it is possible to see which objects have been generated and which not. So for me as a newbie it was a big relief to use this kind of a checklist.


Unbenannt3

You can see all the objects from Oracle Forms and the corresponding generated objects in ADF – with a doubleclick you will directly jump into the generated object (e.g. Java Bean). As a future functionality it is planned to mark the objects that were added by the developer and are considered to be done. So you can work your way through the module without the danger to forget a functionality that was present in Forms up to 100%. The plugin is currently in the beta phase.

Oracle Forms 12c released!

Today Oracle Forms 12c was finally released  – as you can see in Michael Ferrante’s Twitter post, who is the Product Manager for Oracle Forms:

Unbenannt
Check out the Oracle Forms Product Page, where you can find the Release for download:

http://www.oracle.com/technetwork/developer-tools/forms/overview/index.html

So if your application is still running in an version prior to Release 11gR2 you now should really consider to upgrade to be able to use 12c and its features soon. I will download & install 12c in the next days and tell you here what my experience was like.