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

juliojgs · Jun 22, 2022 10:20AM

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?

cormaco · Jun 22, 2022 12:56PM

Check this note in the docs:

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.1/orddg/implicit-parameters.html

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.

thatJeffSmith-Oracle · Jun 23, 2022 6:32PM

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

Jozef Demovic SVK · Jun 22, 2022 11:22AM

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 · Jun 22, 2022 12:56PM

Check this note in the docs:

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.1/orddg/implicit-parameters.html

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.

juliojgs · Jun 23, 2022 8:21AM

https://community.oracle.com/tech/developers/discussion/comment/16839343#Comment_16839343

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 · Jun 23, 2022 6:32PM

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

Forum Stats

Quick Links

Categories

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

Best Answers

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

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

Best Answers

Answers