Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
If I "read" :body_text implicit param in plsql, json params don't bind. Why is that?
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?
Best Answers
-
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. -
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
Answers
-
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
-
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. -
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.
-
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