Skip to Main Content
Forums

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

Jozef Demovic SVK

Hi,
Try to reference implicit parameters in declaration.
Something like:

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

Regards
J

cormaco

Check this note in the docs:
Developer's Guide (0 Bytes)**Note:**The :body implicit parameter must be dereferenced exactly once in a PL/SQL block. If it is dereferenced more than once, then the second and subsequent dereferences will appear to be empty. This is because the client sends the request body only once.You can use either one of the implicit parameters :body or :body_text. Otherwise, the PL/SQL block displays an error message "Duplicate steam parameter''. If you use either :body or :body_text, then you cannot use :bind notation to read attributes of the JSON payload of the request.

1 person found this helpful
juliojgs

it ain't working.
As pointed in the documentation, it is the way it is.
It's a bit counterintuitive in my opinion, mostly the part where the implicit parameters get null values if you reference :body BELOW in the code, but once we know it, we just have to keep it in mind.

thatJeffSmith-Oracle
Answer

You can use one or the other, but not both
json attributes from POST/PUT payload to :bind
entire body of POST/PUT payload to :body or :body_text
and you can only reference :body or :body_text once, if you need it X times in your code block, declare a local variable and use that

Marked as Answer by juliojgs · Jun 22 2022
1 - 4

Post Details

Added on Jun 22 2022
4 comments
1,189 views