Skip to Main Content
  • Questions
  • JSON_VALUE() function with record types

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kosala.

Asked: July 31, 2019 - 3:06 am UTC

Last updated: August 07, 2019 - 10:52 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi,
I followed through the tutorial on Mapping of JSON Data To and From SQL Object Types. This is really great and helpful. But in my case I am trying to use this inside a package and i want to create a RECORD Type from the JSON. Something like below

eg:
TYPE Entity_Person IS RECORD (
   id    VARCHAR2(100),
   name  VARCHAR2(4000),
   age   NUMBER);


FUNCTION Get_Person_From_JSON(
   json_ IN VARCHAR2 ) RETURN Entity_Person
IS
BEGIN
   RETURN JSON_VALUE(json_, '$' RETURNING Entity_Person);
END Get_Person_From_JSON;





Obviously this didn't work. I followed the tutorial and it works on OBJECTS. But does 19c Supports conversion of JSON DATA to and from RECORD Types as well? If yes can you point me what have I done wrong?
Thanks.

and Connor said...

But does 19c Supports conversion of JSON DATA to and from RECORD Types as well?

Unfortuntely not. From the docs:

https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/function-JSON_VALUE.html#GUID-622170D8-7BAD-4F5F-86BF-C328451FC3BE

"You can use SQL/JSON function json_value to instantiate an instance of a user-defined SQL object type or collection type"

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library