Avoiding the Dynamic Action PL/SQL length limit

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.

Error processing row.
ORA-01461: can bind a LONG value only for insert into a LONG column

In order to avoid this problem there are a few options at your disposal.

1. Split the PLSQL into multiple PLSQL actions that will be executed in sequence within the Dynamic Action. This option obviously depends upon the structure of your code and whether or not it will support being refactored into multiple anonymous blocks. This is obviously not ideal as your code was presumably in a single block for a good reason.
2. Move the PL/SQL into a packaged procedure or function and simply call the program unit from within the “Execute PL/SQL Code” action passing in any values as parameters. This also has the added benefit of making that code re-usable throughout your application. If your code represents re-usable business logic then this would arguably be the optimal place to put it anyway.
3. Create an AJAX Callback and call it from the Dynamic Action. When creating Dynamic Actions you are sometimes writing code that is so specific that it is only ever going to be used on the current page, so why not keep all that logic within the page. AJAX Callback processes don’t suffer from the same size limits as the ”Execute PL/SQL Code” action so you can keep all your code in its original format and store it within an appropriately named process. The remainder of this article will demonstrate this technique.

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.

To do this we create an “Execute JavaScript Code” action in place of the “Execute PL/SQL Code” action that we were originally attempting to use. To call the AJAX Callback process we use the apex.server.process function from the Application Express JavaScript API.

apex.server.process("My AJAX Process",
                   {pageItems: "#P1_ITEM1,#P1_ITEM2"},
                   {async: false});

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.

Further information about this and other APEX Javascript API calls can be found here.

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.

In the screenshot below you can see that I have created a new true action of “Execute PL/SQL Code” type to run after my “Execute JavaScript Code” action. Notice that within the PL/SQL code area I have simply entered null because I do not need to run any code. The important part is the “Page Items to Return” field which contains the page item I want to set the on screen value of using the value stored in session.

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.