How to generate Excel Report Oracle Forms and Reports 11gR2

About

We upgraded from 6i to 11gR2 so it took me some time to know how to generate Excel in Oracle Forms and Reports 11gR2. So I wrote it down to help those who couldn't figure it out.In my case I am supposed to generate the Excel file after pressing a certain button,so here is how I did it.

How To

1. Open the form required to generate the Excel report using Oracle Form Builder
2. In the Object Navigator pane go to reports

   

   going to Reports in Object Navigator

3. Select "Create" button

   

   selecting create button

4. A window will appear asking you to choose whether to create new report or use existing one. If you have a source "RDF" file of the report choose "Browse" and select the file

   

   window asking to choose how will you like to add the report

5. After browsing for the report, click "Ok"
6. A new report object will appear in the Object Navigator

   

   here the report appeared automatically named to "REPORT274"

   
   
7. Press F4 to open the Property Palette. In the Report Destination Format type in "ENHANCEDSPREADSHEET"
   
8. Go to the button and add a trigger "WHEN-BUTTON-PRESSED"
   
9. Open the PL/SQL editor to write the trigger function
   DECLARE
   REPORT_ID Report_Object; 
   PARAM_LIST PARAMLIST;
   REPORT_STATUS VARCHAR2(100);
   JOB_ID VARCHAR2(100); 
   REPORT_SERVER_JOB VARCHAR2(254);
   BEGIN
   REPORT_ID := find_report_object('REPORT274');
   
   SET_REPORT_OBJECT_PROPERTY(REPORT_ID,REPORT_SERVER,'report_server_name'); 
   
   -- IN CASE YOU WANT TO CREATE PARAMTER LIST AND PASS IT 
   -- TO THE REPORT
   PARAM_LIST := GET_PARAMETER_LIST('DATA');
   IF NOT ID_NULL(PARAM_LIST) THEN
    DESTROY_PARAMETER_LIST('DATA');
   END IF;
   PARAM_LIST :=CREATE_PARAMETER_LIST('DATA');
    
   ADD_PARAMETER(PARAM_LIST,'PARAMETER',TEXT_PARAMETER,'Y');
   
   REPORT_SERVER_JOB:=RUN_REPORT_OBJECT(REPORT_ID,PARAM_LIST);
   REPORT_STATUS := REPORT_OBJECT_STATUS(REPORT_SERVER_JOB); 
   
   IF REPORT_STATUS = 'FINISHED' THEN 
   JOB_ID := substr(REPORT_SERVER_JOB,instr(REPORT_SERVER_JOB,'_',-1)+1);
   WEB.SHOW_DOCUMENT ('/reports/rwservlet/getjobid'|| JOB_ID ||'?server='||'report_server_name','_blank');
   ELSE 
   MESSAGE ('Report failed, Error message '||REPORT_STATUS); 
   END IF; 
   END;
10. Now Open the Forms from your web browser, and press the excel button which we added the trigger to.
    

    

    the forms open in Internet Explorer, -this is not an original image-

11. The following download window will appear
    
    
12. Save the file and open it with Excel.

Notes

• When opening the file in Excel, Excel may prompt a message asking if you are sure that this is an Excel file or not. you should click yes. However the reason for this error is that the generated file is not ".xls" format rather ".htm" which Excel can open it easily and view it as spread sheet.

Compile Multiple Forms and Multiple Reports For Oracle Forms/Reports 11g

The Problem

Right now In the company we are upgrading our system form Oracle Forms/Reports 6i to Oracle Forms/Reports 11g.
I found myself in need of compiling multiple Forms and Reports - about 1200 each -.
To  Open each and compile is rather dull and exhausting process

The Solution

A- Forms and PLL and menus:

I found a Script about compiling multiple Forms. Here it is how to do it.

1- Create a new file with  Notepad or Notpad++ - I prefer Notepad++ - and name it "compile.bat"

2- Paste The following code into the "compile.bat" file. Note: This code also compile ".pll" and ".mmb" files.

@ECHO OFF 
cls 
Echo libraries compilation.... 
for %%f IN (*.pll) do ( frmcmp userid=scott/tiger@orcl module=%%f batch=yes module_type=library compile_all=yes window_state=minimize & ECHO %%f )
ECHO libraries compilation finished ---
Echo menus compilation.... 
for %%f IN (*.mmb) do ( frmcmp userid=scott/tiger@orcl module=%%f batch=yes module_type=menu compile_all=yes window_state=minimize & ECHO %%f )
ECHO menus compilation finished ---
Echo forms compilation.... 
for %%f IN (*.fmb) do ( frmcmp userid=scott/tiger@orcl module=%%f batch=yes module_type=form compile_all=yes window_state=minimize & ECHO %%f )
ECHO forms compilation finished ---


3- Replace "scott/tiger@orcl" with the connection your connection string "userid/password@databasename" 

4- Place the "compile.bat" file  into the folder that has the your forms

5- Double click the "compile.bat" file to run it.

6- The error logs will be written to file with ".err" extension.

7- Note: In case you don't want to compile a ".pll" file just remove its for loop form the bat file, same with other types.

B- Reports:

I worked it out the same way as I did with the forms. Here it is how to do it

1- Create a new file and name it "rpcnvrt.bat"

2- Paste The following code into the "rpcnvrt.bat" file.

@ECHO OFF
        cls 
        Echo report convertion.... 
        for %%f IN (*.rdf) do ( rwconverter userid=scott/tiger@orcl stype=RDFFILE DTYPE=REPFILE batch=yes OVERWRITE=YES source=%%f & ECHO %%f )
        ECHO report compilation finished ---

3- Replace "scott/tiger@orcl" with the connection your connection string "userid/password@databasename" 

4- Place the "rpcnvrt.bat" file  into the folder that has the your reports

5- Double click the "rpcnvrt.bat" file to run it.

6- The error logs will be written to file with "reports.log" extension.

7- Note: For more options on the rwconverter visit this link.

•  BATCH
• CMDFILE
• COMPILE_ALL
• CUSTOMIZE
• DEST
• DTYPE
• DUNIT
• FORMSIZE
• JVMOPTIONS
• OVERWRITE
• P_AVAILABILITY
• P_DESCRIPTION
• P_FORMATS
• P_NAME
• P_OWNER
• P_PFORMTEMPLATE
• P_PRINTERS
• P_PRIVILEGE
• P_SERVERS
• P_TRIGGER
• P_TYPES
• PAGESIZE
• PARAMETER
• RECURSIVE_LOAD
• SOURCE
• STYPE
• USERID

8- Note: Spaces in the name of the reports seems to be causing an error. Place double quotes around  %%f to be like this "source=%%f".

9- I placed the the attributes for the report conversion in this order because some error happens that I didn't have the time to check it out.

Any questions I will be happy to do my best to answer

Problem using Oracle Forms 11g Migration Assistant

oracle.forms.jdapi.JdapiException: FRM-10102

The problem

I am using Oracle Forms Migration Assistant GUI to upgrade the forms from 6i to 11g. After I choose the module and connect to the database and click finish, I get the following

ERROR opening C:\Users\sbotros\Desktop\FORM\APDPXXFX.fmb :oracle.forms.jdapi.JdapiException: FRM-10102: Cannot attach PL/SQL library Ofg4bsl. This library attachment will be lost if the module is saved.FRM-10102: Cannot attach PL/SQL library Ofg4TEl. This library attachment will be lost if the module is saved.FRM-10102: Cannot attach PL/SQL library Ofg4mes. This library attachment will be lost if the module is saved.

Obviously some of the PL/SQL libraries he can't find

The  Solution

When you run the Migration Assistant you probably run the following file 'frmplsqlconv.bat' and then the Migration Assistant Wizard pops up. What I have noticed that this file has its own FORMS_PATH which it reads from those libraries. P.S. open the file using Notepad++ or other text editors.

.......... 

set FORMS_PATH=%FORMS_PATH%;D:\ORACLE\Middleware\Oracle_FRHome1\forms; 

..........

What I did was add the path where my libraries are so the file became like this

.......... 

set FORMS_PATH=%FORMS_PATH%;D:\ORACLE\Middleware\Oracle_FRHome1\forms;D:\LIB; 

..........

The additional part is in BOLD and voila. run again every thing will run smoothly.

Please leave any comment if something is not clear.

Me

First: introduction,
Name: John Aziz, I am graduate from Computer and System department (CSED2014) Faculty of Engineering Alexandria University, Egypt

Second: why
I created this blog cause I have faced many bugs or problems during my work and I have somehow managed to solve most of them so I want to share what I know with the world somehow to repay the people who helped me solve some of my problems - bugs - that I faced and may be also to encourage others to do so and share their experience.

Thrid: p.s.
I am still new in this blog world, so bear with me if any error occurred, thanks.

Fourth: stackoverflow
my account stackoverflow