Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

If I "read" :body_text implicit param in plsql, json params don't bind. Why is that?

juliojgsJun 22 2022

Hi,
I created a quite simple post handler to insert a record in a database table.
I tested it with header parameters and it works, but if I do it with json payload, the in parameters are null.
After some debugging, I found that I was capturing the :body_text in a local variable (to log the calls).
Whenever I read the :body_text implicit parameter in the plsql, all the json parameters I'm sending in the json request unbind. That's even if I read the :body_text AFTER the custom parameters:

DECLARE
    l_json_doc  CLOB;
Begin
    insert into mytab values (:p_one, :p_two);
    :status_code := 201;
    :errmsg := 'Done! '||:p_one||' - '||:p_two;
    l_json_doc := :body_text;
end;

being the request json:

{
 "p_one": "hello",
 "p_two": "world" 
}

Well, if I comment the line

-- l_json_doc := :body_text;

the insert goes ok.
If not, it insert null values, just for having peeked inside :body_text.
This looks like some kind of quantum mechanics uncertainty ... cannot read both :body_text and parameter values?

This post has been answered by thatJeffSmith-Oracle on Jun 23 2022
Jump to Answer

Comments

Post Details

Added on Jun 22 2022
4 comments
1,148 views