Passing Parameters from Oracle Forms to Oracle Reports

In most enterprise applications built with Oracle Forms and Reports, it is common to run reports directly from a form. But in real scenarios, these reports usually need dynamic input values, such as Employee ID, Department Number, or Date Range, to display meaningful results. This is where passing parameters from Oracle Forms to Oracle Reports becomes crucial.

In this guide, we will explain how to pass parameters step by step, explore different methods, and provide examples so you can implement them easily in your own applications.

Why Pass Parameters to Reports?

Passing parameters ensures that reports show customized and relevant data instead of static information. Some benefits include:

• Dynamic Output: Reports display only the required data (e.g., employees of one department).
• Improved Performance: By filtering data, the report executes faster.
• Flexibility: Same report design can serve different requirements.
• User-Friendliness: Users don’t need to input parameters manually every time.

Methods of Passing Parameters

When you call an Oracle Report from Oracle Forms, you can pass parameters in the following ways:

1. Using REPORT_OTHER Property with RUN_REPORT_OBJECT (Recommended).
2. Passing Parameters in the HOST Command (for client/server setup).
3. Using a Parameter Form within Oracle Reports.

Step 1: Define Parameters in Oracle Reports

Before you can pass parameters, you must define them in your .rdf report.

For example, if you want to filter employees by department:

• Parameter Name: P_DEPTNO
• Data Type: Number
• Default Value: NULL

This parameter will be used inside your report query:

SELECT empno, ename, job, deptno
FROM emp
WHERE deptno = :P_DEPTNO;

Step 2: Create a Report Object in Oracle Forms

1. Open your form in Oracle Forms Builder.
2. Under Reports, create a new Report Object (e.g., REP_EMP).
3. Set properties like Report Filename (e.g., emp_report.rdf) and Report Server.

Step 3: Pass Parameters with RUN_REPORT_OBJECT

Here’s the most common way to pass parameters dynamically:

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

   -- Set Report Properties
   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 parameter P_DEPTNO from form item
   SET_REPORT_OBJECT_PROPERTY(repid, REPORT_OTHER, 'P_DEPTNO=' || :DEPT.DEPTNO);

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

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

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

In this example, if the user selects a department in the form, the report will display employees only from that department.

Step 4: Passing Multiple Parameters

If your report requires more than one parameter, simply concatenate them with spaces:

SET_REPORT_OBJECT_PROPERTY(repid, REPORT_OTHER, 
   'P_DEPTNO=' || :DEPT.DEPTNO || ' P_JOB=' || :EMP.JOB);

This way, the report receives both values at once.

Step 5: Using HOST Command (Client/Server Setup)

For older client/server applications, you can pass parameters via the HOST command:

HOST('rwrun60 report=emp_report.rdf userid=scott/tiger@orcl ' || 
     'destype=screen paramform=no P_DEPTNO=' || :DEPT.DEPTNO);

This method is not recommended for web-deployed applications but works in legacy systems.

Step 6: Using a Parameter Form

Sometimes, reports have their own parameter form. In such cases:

• You can set paramform=YES in the call.
• This allows the user to manually input parameters before running the report.

However, integrating parameters directly from Forms (using REPORT_OTHER) provides a smoother experience.

Best Practices

• Always validate form values before passing them to reports.
• Use descriptive parameter names (e.g., P_START_DATE, P_END_DATE).
• Avoid hardcoding parameters in the PL/SQL code.
• Pass NULL values carefully — handle cases where the parameter is optional.
• Prefer PDF output for better formatting and cross-platform viewing.

Example Use Case

Imagine you have an Employee Form with fields for Department and Job. You want to run a report showing employees of a specific department and job title.

• The user selects Department = 10 and Job = MANAGER.
• The form passes P_DEPTNO=10 and P_JOB='MANAGER' to the report.
• The report displays only managers in department 10.

This makes reporting highly dynamic and user-friendly.

Conclusion

Passing parameters from Oracle Forms to Oracle Reports is a powerful feature that makes reports flexible, dynamic, and user-driven. By defining parameters in the report and using REPORT_OTHER property in Forms, you can filter and customize reports based on real-time form inputs. Whether it’s a single parameter or multiple values, this integration enhances both usability and performance of your Oracle applications.