This post explains how to pass a value from a Apex classic report to a database procedure. Following a discussion of the objective and a high level overview, the post concludes with a practical demonstration.
The example used throughout the post was built using using Apex 19.1 and Oracle Database 19 but this technique should work on previous versions. The Apex page, database procedure and database table can be found on GitHub.
When a user clicks the Select button, the last name will be passed to a database procedure.
The view from 30,000ft
There are a number steps that a developer has to implement to get a handle on a report value. These can quickly become confusing so it is worth taking a step back to look at an overview.
The report link, which in this example is the Select button, fires a custom event along with the report value, the last name. A Dynamic Action listens for the custom event and when received, the first step is set the page item, P2_LAST_NAME with the value from the report. The Dynamic Action next executes PL/SQL code which is the database procedure insert_employee_name. This procedure accepts one parameter, the employee name which it can now obtain from the page item P2_LAST_NAME.
Create a new page item which will contain the report value. In this example the report value will be last_name so the page item has been named P2_LAST_NAME.
Change the Report Link Properties.
Click Target and choose type of URL
For the url enter:
Where ‘lastNameFromReport’ is the name of the event that the Dynamic Action, created in the next step will be listening for and #LAST_NAME# which is the report value.
Create another True action, which this time is going to be used to execute PL/SQL code. Change the Action to Execute PL/SQL code. Under settings enter the database package\procedure. In this example insert_employeee_name is used with the recently set P2_LAST_NAME used as the actual parameter.
insert_employee_name ( p_employee_name => :P2_LAST_NAME );
Items to Submit should be set to P2_LAST_NAME and again Fire on Initialization should be set to no.
With the Apex changes in place, run the report and after selecting a row, such as Employee 101
a select from the table which the database procedure inserts into shows the following:
Thanks to Tobias Arnhold post on running a dynamic action from a report row and for the update to Jeff Eberhard original post.