How to Call Oracle Reports from Oracle Forms

Oracle Forms and Oracle Reports are often used together in enterprise applications. Oracle Forms is designed for data entry and transaction processing, while Oracle Reports is meant for generating professional reports such as invoices, payslips, or analytical summaries. A very common requirement is to call Oracle Reports from Oracle Forms, so that users can generate and view reports directly from their form screens.

In this article, we will go through the different ways of calling reports from forms, the necessary setup, and provide code examples to make the process easy to follow.

Why Call Oracle Reports from Oracle Forms?

Integrating reports with forms provides a seamless experience for users. Here are the main advantages:

• Automation: Users can run reports directly after performing a transaction.
• Consistency: Ensures data in reports matches the form’s current data.
• Flexibility: Allows passing parameters from forms to reports for dynamic output.
• Usability: Users don’t need to separately log into Oracle Reports.

Methods to Call Oracle Reports from Oracle Forms

Oracle Forms provides different approaches to call reports, depending on your application setup:

1. Using RUN_REPORT_OBJECT built-in (Preferred Method).
2. Using HOST command to run reports via the command line.
3. Using WEB.SHOW_DOCUMENT for web-deployed applications.

The most widely used and recommended method is RUN_REPORT_OBJECT, as it provides better control and integration.

Key Steps to Call Reports

Step 1: Create a Report Object in Oracle Forms

1. Open your Form in Oracle Forms Builder.
2. In the Object Navigator, expand Reports.
3. Create a new Report Object (e.g., REP_EMP).
4. In the property palette, set:
   • Report Filename → name of your .rdf file (e.g., emp_report.rdf).
   • Report Server → your Oracle Reports Server name.

Step 2: Write Code Using RUN_REPORT_OBJECT

Here’s an example of how to call a report from a form:

DECLARE
   repid        REPORT_OBJECT;
   v_report_id  VARCHAR2(100);
   v_repserver  VARCHAR2(100);
   v_url        VARCHAR2(2000);
BEGIN
   -- Find the Report Object
   repid := FIND_REPORT_OBJECT('REP_EMP');  -- Report Object Name

   -- Set Report Parameters
   SET_REPORT_OBJECT_PROPERTY(repid, REPORT_COMM_MODE, SYNCHRONOUS);
   SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESTYPE, CACHE);
   SET_REPORT_OBJECT_PROPERTY(repid, REPORT_DESFORMAT, 'PDF');
   SET_REPORT_OBJECT_PROPERTY(repid, REPORT_SERVER, 'rep_server1');

   -- Passing parameters dynamically
   SET_REPORT_OBJECT_PROPERTY(repid, REPORT_OTHER, 'P_DEPTNO=' || :EMP.DEPTNO);

   -- Run the report
   v_report_id := RUN_REPORT_OBJECT(repid);

   -- Get the report URL
   v_url := 'http://myserver:7778/reports/rwservlet/getjobid' || SUBSTR(v_report_id, LENGTH('REP_SERVER1')+2);

   -- Display report in browser
   WEB.SHOW_DOCUMENT(v_url, '_blank');
END;

Step 3: Passing Parameters to Reports

If your report requires parameters, you can pass them dynamically from the form. For example:

SET_REPORT_OBJECT_PROPERTY(repid, REPORT_OTHER, 'P_EMPNO=' || :EMP.EMPNO);

This ensures the report runs only for the selected employee.

Step 4: Alternative Using HOST Command

For desktop-based Forms (not web-deployed), you can use the HOST command:

HOST('rwrun60 report=emp_report.rdf userid=scott/tiger@orcl destype=screen');

However, this approach is less flexible and not recommended for web-deployed applications.

Best Practices for Calling Reports

• Use PDF Format: Setting REPORT_DESFORMAT = 'PDF' ensures cross-platform compatibility.
• Always Use RUN_REPORT_OBJECT: This provides better error handling and integration with Oracle Application Server.
• Pass Parameters Dynamically: Avoid hardcoding parameters to make reports reusable.
• Error Handling: Always check the report return status before showing the document.
• Use Report Server Properly: Ensure the Reports Server is configured and running, otherwise reports won’t generate.

Example Use Case

Suppose you have a Department Form where the user selects a department. You can attach a button labeled “Show Employees Report”. When the user clicks it:

1. The form passes the DEPTNO as a parameter to the report.
2. The report fetches all employees belonging to that department.
3. The output opens as a PDF in a new browser window.

This kind of integration provides a real-time and professional user experience.

Conclusion

Calling Oracle Reports from Oracle Forms is an essential skill for developers working with Oracle applications. By using the RUN_REPORT_OBJECT built-in, you can seamlessly integrate reports with forms, pass parameters dynamically, and present reports in user-friendly formats like PDF. With proper configuration of the Reports Server and good coding practices, you can make your application both powerful and user-friendly.