Return Oracle ORDS REST JSON results in a SQL select

Question

I would like to use Oracle ORDS REST interfaces between two 11g databases. According to external consultants this is faster than using a database link. The receiving 11g database would then have to display the results via an Oracle Apex chart using SQL. I also need to join with tables on the receiving database. Does 11g have the necessary functionality to convert the ORDS REST JSON in order to display it using SQL and what is the needed code for this?

ords doesn't HAVE to return json...if you want to write some code, it can return, CSV, XML...but yeah, upgrade your 11g instance, they'll be completely out of support soon. If you have APEX installed, it can already parse/work with JSON, 11 or 12 or whatever — thatjeffsmith, Jul 15 '20 at 13:17

score 3 · Answer 1 · answered Jul 15 '20 at 09:56

Oracle 11g does not support JSON. That was introduced for the first time in 12c. However, there is a workaround. Oracle Application Express 5.1.4 is compatible with 11g, and it has its own JSON generator ( APEX_JSON ). You need to install it on your database.

Consider the matrix compatibility between Oracle APEX and Oracle database:

For APEX 5.0, the minimum database version is 11.1.0.7
For APEX 5.1, the minimum database version is 11.2.0.4
For APEX 18.1, 18.2, 19.1 the minimum database version is 11.2.0.4

You can retrieve the output of APEX_JSON as CLOB in json format even in 11g.

Example

1.This procedure retrieves the information in JSON using APEX_JSON

CREATE OR REPLACE PROCEDURE get_emp_json (p_empno IN emp.empno%TYPE DEFAULT NULL) AS
  l_cursor SYS_REFCURSOR;
BEGIN
  
  OPEN l_cursor FOR
    SELECT e.empno AS "empno",
           e.ename AS "employee_name",
           e.job AS "job",
           e.mgr AS "mgr",
           TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
           e.sal AS "sal",
           e.comm  AS "comm",
           e.deptno AS "deptno"
    FROM   emp e
    WHERE  e.empno = DECODE(p_empno, NULL, e.empno, p_empno);

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;
END;
/

2.Build the ORDS REST module ( in your own schema )

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v4',
    p_base_path      => 'rest-v4/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v4',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v4',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_json; END;',
    p_items_per_page => 0);
    
  ORDS.define_template(
   p_module_name    => 'rest-v4',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v4',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_json(:empno); END;',
    p_items_per_page => 0);
    
  COMMIT;
END;
/

3.Call the REST WEB SERVICE

http://yourhost:yourport/ords/hr/rest-v4/employees/

Back in the day I used it as Oracle 11g did not handle JSON but only through complicated SQL mechanisms. This way you can build easily web services for retrieving data in JSON format.

score 1 · Answer 2 · answered Jul 15 '20 at 08:25

So there's no built-in JSON support in Oracle 11g. That starts with 12c In your case, I'd built it with the Java support on the database. There are plenty of Java resources to deal with Json (even with the Java 7 which is the supported JVM on the latest 11gR2).

The idea would be to create a (pipelined) table function that outputs the JSON result using a user defined type, for that usecase.

If there is a database link I'd try that first though, because those table functions are not always very performant.

Return Oracle ORDS REST JSON results in a SQL select

2 Answers2