CODEWITHSUNDEEP
oracle-databaserestoracle-apex
Janitha Madushan
Janitha Madushan

Reputation: 1543

How to post JSON using Oracle Rest Data Services

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.

Upvotes: 2

Views: 14731

Answers (2)

GregJarm
GregJarm

Reputation: 460

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/

Upvotes: 2

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

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.

Upvotes: 1

Related Questions