APEX: Programmatically Fetch JSON from DB

Abstract

Although APEX often is called an environment where things favored are done „declarative“ and not programmatically, APEX offers its full strength when you can write PL/SQL and JavaScript as well.

One thing that may happen is, that you may have the need to load data from the database for to „feed“ a visual component with data that may be not available as APEX Component.

So in this chapter I’ll show you how to execute a SQL statement programmatically and process the data returned in your APEX application.

In the next chapter, we will use the functions describe  in this chapter to load data into an Oracle JET Gantt chart, that is currently not available as a APEX component.

Further, we will manipulate the task durations in the Gantt chart by drag & drop, catch the drop event and write the new duration back to the database. Programmatically, not declarative. With a plain Oracle JET Gantt Component, not wrapped into an APEX component…

JSON from database

We can tell the database to send us JSON data in several ways.

• ORACLE APEX_JSON documentation
• Website: Database Development and Administration

So first we write a database function to give us back JSON:

 

FUNCTION SQL2JSON(sql_statement VARCHAR2) RETURN VARCHAR2
  AS
    cur_sql SYS_REFCURSOR;
    p1_json VARCHAR2(32000);
  BEGIN
    OPEN cur_sql FOR sql_statement;
    apex_json.initialize_clob_output;
    apex_json.open_object;
    apex_json.write(cur_sql);
    apex_json.close_object;
    p1_json := apex_json.get_clob_output;
    apex_json.free_output;
    
    RETURN p1_json;
END;

 

Application Process for executing SQL

Then we create application an process that can execute any SQL select and return JSON as data.

The application process is quite simple, as the heavy lifting is done in the previously described PL/SQL function.

begin
    htp.p(SQL2JSON(apex_application.g_x01));
end;

• The function SQL2JSON expects a VARCHAR2 for the SQL statement. It is wrapped into a htp.p call.
• htp.p returns the data from the database to the application.

For me, placing business logic onto the DB server, ideally into a database package seems to be a good idea to me. Having a package is much structured than spreading all the code in dozens of pages where the only way to find the code later on is the magnifying glass of APEX itself.

In the old days, placing SQL into the code of an application was called „embedded SQL“ and a clear indicator for bad coding stlye, and placing too much code into APEX is something that looks quite similar to me.

Other aspects like

• code reusability…
• …especially building libraries
• using the business logic in other applications
• externalize and automate testing of business logic

Javascript to call Application Process

Next, we need a JavaScript function that calls the application process and returns the JSON array to the application

We place this function somewhere where it can be reached from any part of our APEX application. This may be a component on Page 0 or a external JS library that is loaded by the APEX application.

However, the code looks like this:

function getJSON(sqlStatement, callBack){
    apex.server.process(
            'getJSON',            // Process or AJAX Callback name
            {x01: sqlStatement},  // Parameter "x01"
            {
                success: function (pData) {             // Success Javascript
                    var json = pData.substring(1, pData.length - 3);
                    var arr = JSON.parse(json);
                    
                    if (callBack != null){
                        callBack(arr);
                    }
                },
                dataType: "text"                        // Response type (here: plain text)
            }
    );
}

• The SQL statement and a callback function are passed in
• apex.server.process is called with ‚getJSON‘ as first parameter and x01 as SQL statement
• On success, the data (pData) is returned as string, and the first 3 characters have to be cut off
• Then, the rest of the string is parsed as JSON data and returned in the callBack function

Use getJSON in Application

Now, getJSON can be used in the application.

• We write a SQL statement
• We pass it to the JavaScript function getJSON()
• We get back the data in the callback getData(resultData) as array

    var sql = "SELECT * FROM HOLIDAY"
    debugger;
    getJSON(sql, function getHoliday(arr){
        console.log(arr);
    
    });

This code again can be placed in a component on Page 0 or where ever it is needed

Here it is added to a static content page item on page 0:

Once the code is added to page 0, it is executed whenever a page is loaded. As you can see, the content of the SQL request is available in the variable „arr“ now:

Links

• Pro jQuery in Oracle Application Express
  Great book telling how to combine JavaScript and jQuery skills with APEX.
• ORACLE APEX_JSON documentation
• Website: Database Development and Administration