Forum Stats

  • 3,839,759 Users
  • 2,262,532 Discussions


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


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:

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

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?

Best Answers

  • cormaco
    cormaco Member Posts: 1,959 Silver Crown

    Check this note in the docs:

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,705 Employee
    Answer ✓

    You can use one or the other, but not both

    1. json attributes from POST/PUT payload to :bind
    2. entire body of POST/PUT payload to :body or :body_text
    3. 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