Loading/Inserting JSON data into Apex Table using ORDS Rest API

Question

I have a requirement to load data [ JSON format ] into an APEX table via ORDS Rest API.

So , I have created a POST handler within a module and below is the PL/SQL code for the handler

PL/SQL

begin
insert into PEEP(USER_ID,NAME,AGE,PROFESSION,LOCATION) values (:val1,:val2,:val3,:val4,:val5);
end;

CURL Command:

curl -s -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{"val1":"121","val2":"Rachel","val3":"37","val4":"Psychologist","val5":"Melbourne"}' https://xxxxx/ords/apex/peep/uploadinfo

The problem with the above approach is it inserts only one row with a single API call and if I give multiple rows with -d then it only inserts the first row and ignores the second row/input

Example

{"user_id" : 111,"name":"Sam","age":29,"profession":"Saxophonist","location":"Sydney"},{"user_id" : 121,"name":"Rachel","age":37,"profession":"Psychologist","location":"Melbourne"}

My requirement is to either take a JSON file as an input with the Curl Command or give multiple inputs with -d in curl command

Can someone please help me with this?

Hi Manoj, did you still need help with this or did Koen’s answer work? If it worked, please accept the answer for future viewers. — Dan McGhan
– Dan McGhan, Commented May 26, 2020 at 15:56

Koen Lostrie · Accepted Answer · 2020-05-22 16:09:02Z

2

Your pl/sql body only accepts a single row, so anything after that will be ignored. You need to reference the entire payload using :body and loop through it using the JSON_TABLE syntax in oracle. Jeff Smith has written a nice article abou that here: www.thatjeffsmith.com. The example in his code is not exactly the same, he is using a single complex structure. In your case you need to loop through the data instead.

You'll have to use json_table to loop through the elements and process the elements row by row.

Start with testing your code in sqldeveloper with hard coded json, similar to the example below.
Once you have your sql statement working in sqldeveloper, incorporate it in the pl/sql body of your rest handler. Put it in a for loop and try inserting the rows. Test/debug
Once it works, replace the hardcoded json with ":body". Test/debug

Example of how to convert json to rows (using records from the emp table, I just took 2 columns for the sake of the test but you get the point)

SELECT 
  empno,
  ename
FROM 
  JSON_TABLE('{"items":
[
{"empno":7839,"ename":"KING","job":"PRESIDENT","hiredate":"17-NOV-1981","sal":5000,"deptno":10}
,{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"01-MAY-1981","sal":2850,"deptno":30}
]
}' , '$.items[*]'
           COLUMNS (
             empno  NUMBER   PATH '$.empno',
             ename  VARCHAR2(50)   PATH '$.ename'
             ));

edited May 22, 2020 at 16:09

answered May 22, 2020 at 7:39

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Manoj Kumar Over a year ago

Can you please help with the PL/SQL for my case

Koen Lostrie Over a year ago

What do you need help with ? I'm happy to help you if you have a specific question but I'm not going to do it for you. Start with writing the sql to extract the column values from your json. Test it. This part usually takes a bit of time. But there is plenty of documentation and examples on the web about it. I noticed that the "json" you have posted above is not valid json at all. It is 2 json elements but together it is not valid json so you'll have to fix that first. Once you have that first part working combine it with the examples in the blog I posted earlier.

Manoj Kumar Over a year ago

Hi, If my pass JSON in below format too it doesnt take {"items" : [{"user_id" : 211,"name":"Sam","age":29,"profession":"Saxophonist","location":"Sydney"},{"user_id" : 212,"name":"Rachel","age":37,"profession":"Psychologist","location":"Melbourne"}]}

Manoj Kumar Over a year ago

The problem I think is with my PL/SQL where its not taking in CLOB format. Can you please help me in enhancing my PL/SQL code?

Koen Lostrie Over a year ago

updated my answer with example. If you have followup questions, please provide detail. "doesn't take" is not helpful. Read documentation, provide code snippets, error messages, explain what you have tried, what the error messages are how you have reached it. The blog post I have referred to is very complete. In combination with my updates you should be good to go.

Collectives™ on Stack Overflow

Loading/Inserting JSON data into Apex Table using ORDS Rest API

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related