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.

The objective

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.

Apex changes

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:

javascript:$.event.trigger('lastNameFromReport','#LAST_NAME#');

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 a new Dynamic action, setting Event to custom, Custom Event to lastNameFromReport, Selection Type to JavaScript Expression and for JavaScript Expression enter document. The screen shot below illustrates how the Dynamic Action should look at this point.

For the first true action, set Action to SetValue, Set Type to JavaScript Expression and in the JavaScript Expression enter this.data and for Affected Elements set Selection Type to Item(s) and then Item(s) P2_LAST_NAME. The final change for this step is to set the Fire On Initialization to No. The screen shot below shows how these properties should look:

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.

Testing

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:

Acknowledgements

Thanks to Tobias Arnhold post on running a dynamic action from a report row and for the update to Jeff Eberhard original post.