Serializing/marshalling package level user define record types to JSON

JSON_OBJECT_T does not have a constructor that takes in a record type so you will need to explicitly define each of the keys/values to define the JSON. JDOM_T is not required for what you are attempting to do. Below is an example on how to convert your record types to JSON and back.

DECLARE    TYPE RT_Address IS RECORD    (        Line1    VARCHAR2 (100),        Line2    VARCHAR2 (100),        City     VARCHAR2 (100)    );    TYPE RT_Customer IS RECORD    (        FirstName    VARCHAR2 (100),        LastName     VARCHAR2 (100),        Address      RT_Address    );    l_customer1   rt_customer        := rt_customer ('John', 'Doe', rt_address ('123 Main Street', 'Apartment# 2A', 'London'));    l_customer2   rt_customer        := rt_customer ('Jane', 'Smith', rt_address ('456 Broken Dreams Blvd', NULL, 'Greenville'));    l_json        json_object_t;    l_record      rt_customer;    FUNCTION customer_record_to_json (P_Customer RT_Customer)        RETURN json_object_t    IS        l_address    json_object_t := json_object_t ();        l_customer   json_object_t := json_object_t ();    BEGIN        l_address.put ('Line1', p_customer.address.line1);        l_address.put ('Line2', p_customer.address.line2);        l_address.put ('City', p_customer.address.city);        l_customer.put ('FirstName', p_customer.firstname);        l_customer.put ('LastName', p_customer.lastname);        l_customer.put ('Address', l_address);        RETURN l_customer;    END;    FUNCTION customer_json_to_record (p_customer_json json_object_t)        RETURN rt_customer    IS        l_address_json   json_object_t := json_object_t ();        l_address        rt_address;        l_customer       rt_customer;    BEGIN        l_address_json := p_customer_json.get_object ('Address');        l_address.line1 := l_address_json.get_string ('Line1');        l_address.line2 := l_address_json.get_string ('Line2');        l_address.city := l_address_json.get_string ('City');        l_customer.firstname := p_customer_json.get_string ('FirstName');        l_customer.lastname := p_customer_json.get_string ('LastName');        l_customer.address := l_address;        RETURN l_customer;    END;BEGIN    l_json := customer_record_to_json (l_customer1);    DBMS_OUTPUT.put_line ('Customer 1 (JSON): ' || l_json.stringify);    l_record := customer_json_to_record (l_json);    DBMS_OUTPUT.put_line ('Customer 1 (Record) (FirstName): ' || l_record.firstname);    DBMS_OUTPUT.put_line ('Customer 1 (Record) (LastName): ' || l_record.lastname);    DBMS_OUTPUT.put_line ('Customer 1 (Record) (Line1): ' || l_record.address.line1);    DBMS_OUTPUT.put_line ('Customer 1 (Record) (Line2): ' || l_record.address.line2);    DBMS_OUTPUT.put_line ('Customer 1 (Record) (City): ' || l_record.address.city);    l_json := customer_record_to_json (l_customer2);    DBMS_OUTPUT.put_line ('Customer 2 (JSON): ' || l_json.stringify);    l_record := customer_json_to_record (l_json);    DBMS_OUTPUT.put_line ('Customer 2 (Record) (FirstName): ' || l_record.firstname);    DBMS_OUTPUT.put_line ('Customer 2 (Record) (LastName): ' || l_record.lastname);    DBMS_OUTPUT.put_line ('Customer 2 (Record) (Line1): ' || l_record.address.line1);    DBMS_OUTPUT.put_line ('Customer 2 (Record) (Line2): ' || l_record.address.line2);    DBMS_OUTPUT.put_line ('Customer 2 (Record) (City): ' || l_record.address.city);END;

Update

It is possible to just JSON_OBJECT(*) to create a JSON object, but the * expansion will not work with record types as you will get a ORA-40579: star expansion is not allowed.

If you do wish to use JSON_OBJECT to create the JSON instead of JSON_OBJECT_T, the types you are using will need to be predefined (not in the pl/sql block) and you will still need to define each field in the JSON structure. You will also need to define a table type of the RT_CUSTOMER type so that you can query from it.

CREATE TYPE RT_Address AS OBJECT (Line1 VARCHAR2 (100), Line2 VARCHAR2 (100), City VARCHAR2 (100));CREATE TYPE RT_Customer AS OBJECT(    FirstName VARCHAR2 (100),    LastName VARCHAR2 (100),    Address RT_Address);CREATE TYPE rt_customer_t AS TABLE OF rt_customer;SELECT json_object (           'firstname' VALUE firstname,           'lastname' VALUE lastname,           'address' VALUE               json_object ('line1' VALUE TREAT (address AS rt_address).line1,                            'line2' VALUE TREAT (address AS rt_address).line2,                            'city' VALUE TREAT (address AS rt_address).city)) as customer  FROM TABLE (           rt_customer_t (               RT_Customer ('John',                            'Doe',                            rt_address ('123 Main Street', 'Apartment# 2A', 'London')),               RT_Customer ('Jane',                            'Smith',                            rt_address ('456 Broken Dreams Blvd', NULL, 'Greenville'))));