Calling PLSQL from Javascript

For those who came from an Oracle Forms world, it is very easy to start working with APEX. When creating simple screens, you don’t need any jQuery or AJAX knowledge as dynamic actions help you. However, there comes a point where applications get more complex and we need to add parameters, make things conditional, deal with error messages etc…
In this blog, I will show how to call a database procedure passing a parameter which returns a result without submitting the page.
For the example, I have a procedure called process order, which returns an error message and an error code. If the return is successful, then the error code will be 0, if not it will return an error code.

1. AJAX Callback

Let’s start with the ajax process responsible for calling the PL/SQL procedure.

On the APEX page go to the Processing tab, right-click on Ajax Callback and click on Create Process.

In the PL/SQL Code window, add the code below to call the database procedure.

Declare
   ln_error_code         Number;
   lv_error_msg          varchar2(4000);   

Begin
FOR i IN 1..apex_application.g_f01.COUNT
LOOP
PROCESS_ORDER (p_order_num => apex_application.g_f01(i),
               p_return_code => ln_error_code ,
               p_return_message => lv_error_msg);
END LOOP;

/* below, the function return a JSON formatted string with the two returned values */
apex_json.open_object;
  apex_json.open_array('output');
    apex_json.open_object;
      apex_json.write('lv_error_code', ln_error_code);
       apex_json.write('lv_error_msg', lv_error_msg);
  apex_json.close_object; 
   apex_json.close_array;
apex_json.close_object;

End;

2. JavaScript function to call the AJAX Process

In the page properties level go to Function and Global Variable Declaration to create the JavaScript function.

function processOrder(id) {
    /* to avoid the user to click on some other order while a order is being processed,
        we show the apex spinner, you need to set a ID on the report. in this case the ID is ORDER_REPORT */
    var lSpinner$ = apex.util.showSpinner( $( "#ORDER_REPORT" ) );
    /* ajax to call the database procedure */
    apex.server.process("PROCESS_ORDER", { // the ajax callback process name
        f01 : id, /* The order id passed from the report link */
        pageItems: "#P1_ORDER_ID" // The page item that we want to submit before calling the process.
    }, {
        success: function(pData) {
            /* now we can remove the spinner */
            lSpinner$.remove();
            
            /* The Ajax process will return lv_error_msg and lv_error_code, if lv_error_code = 0 
            show the successful message, if not show the error */ 
            var errorMsg = pData.output[0].lv_error_msg; 
            if (pData.output[0].lv_error_code == '0') {
                apex.message.clearErrors();
                apex.message.alert( 'Order processed successfully' );
            } else {
                apex.message.clearErrors();
                apex.message.showErrors([{
                    type: "error",
                    location: ["page"],
                    message: errorMsg,
                    unsafe: false
                }]);
            }
        }
    });
}

From APEX 18, instead of calling apex.util.showSpinner on the apex.server.process there is parameter called loadingIndicator. Details here: https://goo.gl/sRg8wZ

3. Create the order report to call the JavaScript function

I am using the DEMO_ORDERS table from the sample database package application, but you can use your own.

3.1 Edit the order id column to call the JavaScript function.
Type: Link
Target: Type: URL / Target: javascript:processOrder(#ORDER_ID#);
Link Text: #ORDER_ID#
Link Attributes: class=”t-Button t-Button–simple t-Button–hot t-Button–stretch”

Run the report to show the results. In this example the order #2 returns a successful message, #3 returns an error.

If you would like to find out more information speak to one of our Oracle APEX experts, get in touch through enquiries@dsp.co.uk or book a meeting...

Author: Rodrigo Mesquita 

Job Title: Oracle APEX Development Consultant

Bio: Rodrigo is an Oracle ACE and APEX Developer Expert certified by Oracle and is an experienced software engineer with emphasis in analysis, design and development of bespoke Oracle applications utilising development tools such as PL/SQL, APEX and Forms for a variety of international businesses. Rodrigo speaks regularly at Oracle community events and enjoys creating APEX plugins and writing blogs.