Home › Discussions › Questions & Answers › Implementing PL/SQL Triggers for Report Execution

Implementing PL/SQL Triggers for Report Execution

Avatar photoCustomer Questions & Answers 9 replies

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?

9 replies

  • Support Enginatics

    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 , .

  • Avatar photoCustomer

    What naming conventions must the functions inside my custom DB Package follow for the triggers to work correctly?

  • Support Enginatics

    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 .

  • Avatar photoCustomer

    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?

  • Avatar photoCustomer

    Can you provide a practical example of a standard Oracle process requiring a ‘Before report trigger’?

  • Support Enginatics

    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 Enginatics

    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 .

  • Avatar photoCustomer

    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 Enginatics

    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 , , .

  • You must be logged in to reply to this post.