How can I load JSON data into Oracle using a ORDS Rest API?

Question

I am trying to follow along with a tutorial on how to get a JSON object loaded into a ORDS table. I have this working using a standard curl with 1 record at a time but it would be great if I could feed a large file that the is then read in my REST handler via JSON_TABLE.

Tutorial link : https://www.thatjeffsmith.com/archive/2019/03/using-the-database-to-manipulate-json-from-your-post-body-requests-in-ords/

The code I am trying to compile:

create or replace PROCEDURE create_employee (
  p_empno     IN  emp.empno%TYPE,
  p_ename     IN  emp.ename%TYPE,
  p_job       IN  emp.job%TYPE,
  p_mgr       IN  emp.mgr%TYPE,
  p_hiredate  IN  VARCHAR2,
  p_sal       IN  emp.sal%TYPE,
  p_comm      IN  emp.comm%TYPE,
  p_deptno    IN  emp.deptno%TYPE
)
AS

BEGIN

  INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  SELECT 
    empno, ename, ejob, mgr, hiredate, sal,comm, deptno
FROM 
  JSON_TABLE(:body_text, '$'
           COLUMNS (
             empno  NUMBER   PATH '$.empno',
             ename  VARCHAR2(50)   PATH '$.ename',
             ejob  VARCHAR2(50)   PATH '$.ejob',
             mgr  VARCHAR2(50)   PATH '$.mgr',
             hiredate  VARCHAR2(50)   PATH '$.hiredate',
             sal  VARCHAR2(50)   PATH '$.sal',
             comm  VARCHAR2(50)   PATH '$.comm',
             deptno  VARCHAR2(50)   PATH '$.deptno'
             ));
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
COMMIT;
END;

If I replace the :body_text, '$' with the actual JSON it works great and displays multiple rows exactly like I need.

Currently facing the following error: Error(19,14): PLS-00049: bad bind variable 'BODY_TEXT'

Example of load file.

[
{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":7839,"hiredate":"17-NOV-1981","sal":5000,"comm": null,"deptno":10}
,{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"01-MAY-1981","sal":2850,"comm": null,"deptno":30}
]

That link points to a stack overflow question, not really a tutorial. — Koen Lostrie
– Koen Lostrie, Commented Jul 9, 2021 at 14:59

Koen Lostrie · Accepted Answer · 2021-07-09 15:20:10Z

In that blog post, Jeff is putting that code in the source of the post handler, you're putting it in a pl/sql stored procedure. That does not compile because the bind variable :body_text does not exist in the scope of that procedure.

It should work if you put the following code in the source of the POST handler (not using the stored procedure):

BEGIN
  INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  SELECT 
    empno, ename, ejob, mgr, hiredate, sal,comm, deptno
FROM 
  JSON_TABLE(:body_text, '$'
           COLUMNS (
             empno  NUMBER   PATH '$.empno',
             ename  VARCHAR2(50)   PATH '$.ename',
             ejob  VARCHAR2(50)   PATH '$.ejob',
             mgr  VARCHAR2(50)   PATH '$.mgr',
             hiredate  VARCHAR2(50)   PATH '$.hiredate',
             sal  VARCHAR2(50)   PATH '$.sal',
             comm  VARCHAR2(50)   PATH '$.comm',
             deptno  VARCHAR2(50)   PATH '$.deptno'
             ));
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
COMMIT;
END;

I believe an example of what you are looking for is shown here: https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-restful-web-services-handling-complex-json-payloads . It showcases the different possibilities of handling a json payload using a stored procedure. As you see, the stored procedures in that article are all created with a blob argument that will hold the json object. Note that if you're using a blob, then the bind variable is :body, for clob it is :body_text

Collectives™ on Stack Overflow

How can I load JSON data into Oracle using a ORDS Rest API?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related