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}
]
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