Implementing PL/SQL Triggers for Report Execution

Customer December 20, 2022 at 8:05 pm

I need to perform two specific actions related to my report: first, update a temporary staging table before data extraction, and second, clean up that table after the report finishes. Where do I define these pre- and post-processing steps?

Viewing 8 reply threads

Author
Replies
- Support December 21, 2022 at 1:39 am
  You define these steps using the Before report trigger and After report trigger options, located in the Report Options section of the Blitz Report setup . These fields reference a custom DB Package that contains the necessary PL/SQL functions . The ‘Before report trigger’ is ideal for data preprocessing, such as updating staging tables, while the ‘After report trigger’ is suitable for post-processing tasks like clean up or calling standard Oracle PL/SQL code , .
- Customer December 21, 2022 at 7:07 pm
  What naming conventions must the functions inside my custom DB Package follow for the triggers to work correctly?
- Support December 23, 2022 at 2:49 am
  The functions within your custom DB Package must adhere to specific naming conventions: `afterpform`, `beforereport`, and `afterreport` . Furthermore, these functions are required to return the boolean data type . For example, the `beforereport` function would execute your staging table update logic before the main report SQL runs .
- Customer December 23, 2022 at 11:22 am
  If my report uses parameters (e.g., Operating Unit), do these parameter values get passed to the functions in the DB Package, and how do I reference them in the PL/SQL code?
- Customer December 24, 2022 at 2:20 pm
  Can you provide a practical example of a standard Oracle process requiring a ‘Before report trigger’?
- Support December 25, 2022 at 4:00 am
  Yes, parameter values are passed to the custom package functions . The runtime values of the Blitz Report parameters become available inside the package body through corresponding variables that match the parameter names in the report’s parameter anchors . This allows your PL/SQL logic (e.g., in `beforereport`) to condition its actions based on the user-selected parameters .
- Support December 26, 2022 at 11:54 am
  A highly practical example is the FA Depreciation Projection report . Since the report data relies on Oracle calculations, it requires running the standard Oracle ‘Depreciation Projection’ concurrent program first . The Blitz Report references a custom database package (e.g., `XXEN_FASPRJ`) which submits and waits for the completion of that concurrent program *before* the main report SQL executes, ensuring the data is current and complete .
- Customer December 27, 2022 at 8:25 am
  If I need to run a shell script on the application server after the report completes, should I use the ‘After report trigger’ or a different feature?
- Support December 28, 2022 at 2:34 pm
  To execute a Unix shell script on the application server, you should use the ‘Custom Postprocess’ option, which is separate from the PL/SQL database triggers . The script must be located under the `$CUSTOM_TOP/bin/custom/` directory (e.g., `$XXEN_TOP/bin/custom/generate_and_send_pdf.sh`) and is invoked with several run-time parameters, including the output filename and parameter values, allowing it to handle distribution or external processing , , .
Author
Replies