Return JSON into javascript object from Oracle APEX

Question

Is there a way to populate a client side javascript object, for use in visualisation libraries like D3 or Paper.JS via ORACLE APEX, without the ORDS REST service?

i.e. can I use the javascript API, or PL/SQL (APEX_JSON?) for example to run a query, and return into a client side javascript object the json response?

I know how to return JSON into a PL/SQL Dynamic Content region for example using PL/SQL (see below), but if I just want the data to get returned into a client side javascript object - whats the process? The query below does output formatted JSON into the REGION output, but I just want the json in memory as a javascript array, ready to pass to another javascript library.

declare 
l_cursor sys_refcursor;
BEGIN
   OPEN l_cursor for SELECT A.UID, 
cursor(SELECT PREFERRED_NAME FROM DIM_PRODUCT WHERE PRODUCT_ID = A.PRODUCT_ID) as PRODUCTS from ARIEL.MARKETED_PRODUCT A WHERE STATE = 'C';
   APEX_JSON.write(l_cursor);   
END;

Regards

Stephen

kfinity kfinity · Accepted Answer · 2019-09-18T14:37:04

It sounds like you want to do an AJAX request and have it return JSON into a client-side javascript object. There's two parts to this - PL/SQL on the server side, and javascript on the client side.

On the server side, the easiest way is to set up an On-Demand Process that returns your JSON. It sounds like your block above works fine for that.

On the client side, you can either use the Application Express JavaScript API or jQuery (the APEX JS stuff is just a convenience wrapper around jQuery).

Example client-side AJAX call:

apex.server.process ( "MY_PROCESS", {
  x01: "test",
  pageItems: "#P1_DEPTNO,#P1_EMPNO"
  }, {
 success: function( pData ) { ... do something here ... }
  } );

In this example, pData is the JSON returned from your On-Demand Process - whatever you want to do with it, you should do in the success callback function.

Three parameters are being sent to the On-Demand Process named MY_PROCESS: x01, which can be accessed in PL/SQL as apex_application.g_x01 or :APP_AJAX_X01, and two page items, which are added to session state and can be accessed the usual way (:P1_DEPTNO, etc).

See also this article on how to test an On-Demand Process.

Return JSON into javascript object from Oracle APEX

1 Answers