How to post JSON using Oracle Rest Data Services

Question

I am using Oracle regular database(not like NoSQL or something) with oracle rest data services. I need to know how to fetch the HTTP message body using pl/sql. For an example,

send json object to the oracle application express through oracle rest data service, inside the application express expose the message body and get the content structured with json and fetch the fields one by one.

Inside PL/SQL for the RESTful service in the Application Express SQL Workshop you can reference the value of the body with the :body (colon body) bind variable. — GregJarm
– GregJarm, Commented Feb 4, 2015 at 21:15
thank you, could you give me an example, then how to fetch the values. For an example I am using json inside the body. then how to fetch the fields inside. — Janitha Madushan
– Janitha Madushan, Commented Feb 5, 2015 at 2:37
At the moment, unless you have 12c database you will need custom code to retrieve JSON values. I suggest the PL/JSON package (github.com/pljson/pljson). Additionally you need to convert the Body from a Blob to a Clob to process it. I'll post some code in an answer but I this is not a full example of all the required pieces just yet. — GregJarm
– GregJarm, Commented Feb 6, 2015 at 17:42

mvermand · Accepted Answer · 2019-05-31 04:22:24Z

2

I deleted my earlier post. You can get the body as BLOB and convert to a character to work with but at least in the 4.2 version of the Application Express hosted instance at apex.oracle.com I got this to work with automatic JSON value binding.

Begin
  htp.p('JSON ename = ' || :ename);
  htp.p('JSON dept = ' || :dept);

  insert into emp(ename, deptno)
    values (:ename, :dept);
  htp.p('Rows Inserted = ' || sql%rowcount);

end;

You can test it by posting something like this:

{ "ename": "Seema", "dept": 10}

to the demo at : https://apex.oracle.com/pls/apex/gjarmiolowski/hr/employees/

make sure the Content-Type header is set to application/json

UPDATE: here's a windows curl command that works curl --header "Content-type: application/json" --request POST --data-ascii "{\"ename\":\"hello\",\"dept\":\"10\"}" https://apex.oracle.com/pls/apex/gjarmiolowski/hr/employees/

edited May 31, 2019 at 4:22

mvermand

6,1578 gold badges50 silver badges79 bronze badges

answered Feb 6, 2015 at 19:04

GregJarm

4604 silver badges7 bronze badges

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

2 Comments

GregJarm Over a year ago

This will only work for a simple flat single json. No lists and no nesting is possible with this example. If you need something more compicated you might look into the pl/json library and use dbms_lob.converttoclob to change the :body to a clob to parse it.

mvermand Over a year ago

For complex payloads, have a look at oracle-base.com/articles/misc/…

Olafur Tryggvason · Accepted Answer · 2015-07-02 23:23:13Z

If you install the latest APEX 5.0 which was released on April 15. You get access to a great API to work with JSON

I'm using it on 11.2 and have been able to crunch every single json, from simple to very complex objects with multiple arrays and 4/5 levels. APEX_JSON

If you do not want to use APEX. Simply install the runtime environment to get access to the API.

Example of a (real) POST method that accepts a very complex JSON object. Array with multiple objects and sub-arrays 3-5 levels deep. Oracle Apex POST json example

I take the whole body and send it to a package for crunching. (the :body). the :apikey comes with the header (not applicable here).

:body is blob so you package needs to convert it to clob and do any character set conversion if your database isn't UTF8.

After that you take the clob and parse it as json.

apex_json.parse (p_source => l_clob);

In my case the initial object is an array so I loop through all the items with

for i in 1 .. nvl (apex_json.get_count (p_path => '.'), 0) loop

Example of finding a varchar2 value

l_app.medication_description := apex_json.get_varchar2 ('[%d].regularMedicationDetails', i);

Where the "i" is referencing the nth object in the array,.

As a veteran PL/SQL programmer, this setup as blasted me into the modern web driven world.

Collectives™ on Stack Overflow

How to post JSON using Oracle Rest Data Services

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related