Blog

APEX How to

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.

Rodrigo Mesquita Contributor
Oracle APEX Development Consultant , Explorer UK Ltd

Rodrigo is a Development Consultant at Explorer. Rodrigo has an Oracle APEX Developer Expert Certification issued 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.

follow me

Dive straight into the feedback!
Login below and you can start commenting using your own user instantly