Dynamic Actions are a great way to execute PL/SQL code without having to wait for a page submit, but if you have ever used them with any reasonably complex PL/SQL code you may have come across a limitation currently present in Apex 4.2 and below.
Any attempt to create an “Execute PL/SQL Code” action, within your Dynamic Action, where the PL/SQL is more than 4000 characters in length will result in the following error.
In order to avoid this problem there are a few options at your disposal.
First we need to take our existing PL/SQL code and create a new AJAX Callback process on the page. This is achieved by right-clicking the AJAX Callbacks tree node at the bottom of the Page Processing area and selecting “Create”. Make sure to choose a “PL/SQL” process type, and to make a note of the name you give your process as we will need the exact value used later. Also make sure that the process point has defaulted to “On Demand”. Enter your PL/SQL code when prompted and click the create button. Once created you should see your new process nested under the AJAX Callbacks tree node.
The next thing we need to do is to call the AJAX Callback process from the appropriate place in our Dynamic Action.
apex.server.process("My AJAX Process",
In the below example I am calling an AJAX Callback process called “My AJAX Process”. The first parameter is the name of the process. Note that the name is case sensitive and must match exactly the value that you entered when creating your AJAX Callback process.
The second parameter is a list of the items that I want to submit into session to be used as part of my AJAX Callback. Each item name has a # in front of it which is the JQuery identifier syntax.
The third parameter is important to include if you are executing your PL/SQL as one of a number of actions that must be executed in sequence within your Dynamic Action. The default behavior of apex.server.process is to execute the process asynchronously, meaning that it doesn’t wait for the process to finish before moving on. As a result if you have subsequent actions that depend on the output of your AJAX process there will be no guarantee that the process will have finished executing prior to the next action commencing. To prevent this we can force the process to run synchronously which means the next action in our Dynamic Action will definitely not fire until this process has finished executing.
The next and final step to this solution is optional depending on whether or not your AJAX process needs to return any values to the screen. There are a number of different ways this can be achieved however I have found that the simplest solution is to make use of the built in behavior of the “Execute PLSQL Code” action.
For example, if within my AJAX Callback process I am setting the value of an item (P1_ITEM3).
:P1_ITEM3 := :P1_ITEM1 || ' ' || :P1_ITEM2;
All I need to do is to create an “Execute PLSQL Code” action that will return P1_ITEM3’s new value from session to my screen.
Now when the Dynamic Action runs it will call your AJAX Callback process and then return the appropriate item values to the screen completely bypassing the 4000 character limit imposed by the “Execute PL/SQL” action.
Author: Craig Sykes
Job Title: Senior Oracle Development Consultant
Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.