CODEWITHSUNDEEP
oracle-databaseoracle-apex
Janitha Madushan
Janitha Madushan

Reputation: 1543

Oracle Rest Data Services POST using JSON

I am using oracle rest data service in application express(apex). I need to know how to update/insert the database, using Json formatted http request. Actually what I need to know,

A request is Json formatted request from REST client,

{
  "items" : [
    {"PROJECTID"       : 1},
    {"PROJ_NAME"       : "rest"},
    {"PROJ_CLIENT"     : "Sam"}
  ],
  "hasMore" : false
}

how to insert/update database in apex,

begin
insert into projects(PROJECTID,PROJ_NAME,PROJ_CLIENT) 
values (:PROJECTID,:PROJ_CLIENT,:PROJ_NAME);
commit;
end;

Upvotes: 3

Views: 3953

Answers (2)

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

Update to apex 5.0. There is a great json library that ships with that version.
https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29635

Works in 11g and 12c SE. I use it for really complex POST data in ORDS.

Lets take your example (note that its a pretty weird json object, would not make it like that):

declare
    /* this would be :BODY */
    l_json   varchar2 (200) := '{
  "items" : [
    {"PROJECTID"       : 1},
    {"PROJ_NAME"       : "rest"},
    {"PROJ_CLIENT"     : "Sam"}
  ],
  "hasMore" : false
}';
begin
    apex_json.parse (l_json); /* use :body on the rest service */
    dbms_output.put_line (apex_json.get_number ('items[%d].PROJECTID', 1));
    dbms_output.put_line (apex_json.get_varchar2 ('items[%d].PROJ_NAME', 2));
    dbms_output.put_line (apex_json.get_varchar2 ('items[%d].PROJ_CLIENT', 3));
    if apex_json.get_boolean ('hasMore') then
        dbms_output.put_line ('True');
    else
        dbms_output.put_line ('False');
    end if;
end;
==>
PL/SQL block executed
1
rest
Sam
False

I think the json object should probably be like this:

{
    "items": [
        {
            "PROJECTID": 1,
            "PROJ_NAME": "rest",
            "PROJ_CLIENT": "Sam"
        }
    ],
    "hasMore": false
}

Upvotes: 1

Janitha Madushan
Janitha Madushan

Reputation: 1543

I found the answer, This cannot be done easily. First we need to get the body of the request. This can be done using the bind variable of body (:body). But this body variable is in blob datatype. 

blob_body BLOB := :body;

Then convert this into CLOB variable,

clob_variable CLOB := convert_to_clob(blob_body);

Now the easiest way to abstract this, is convert to 'varchar2' and use library like PL/JSON to exract the data inside the json.

Upvotes: 3

Related Questions