0

I'm trying to use User-Defined Datatypes in Oracle to handle flexible fields in an application:

CREATE OR REPLACE TYPE udf_typ AS OBJECT (
  name           VARCHAR2(4000),
  value          VARCHAR2(4000),
  MAP MEMBER FUNCTION to_json return VARCHAR2  
);
/

CREATE OR REPLACE TYPE BODY udf_typ AS 
    MAP MEMBER FUNCTION to_json RETURN VARCHAR2 IS
    BEGIN
        RETURN JSON_OBJECT('name' VALUE SELF.name, 'value' VALUE SELF.value);    
    END to_json;
END;
/

CREATE OR REPLACE TYPE udf_list_typ AS TABLE OF udf_typ; -- nested table type
/

As you can see from the code above, udf_typ has a to_json function and I would like to also implement a similar function for udf_list_typ but I can't figure out how.

How do I write a member function for udf_list_typ that will return a JSON array containing all members of the nested table (E.g. using JSON_ARRAYAGG) ?

Also, when I query a table with a column of type UDF_LIST_TYP via SqlDeveloper I see something like:

SYS.UDF_LIST_TYP([SYS.UDF_TYP], [SYS.UDF_TYP], [SYS.UDF_TYP], [SYS.UDF_TYP], [SYS.UDF_TYP])

Is it possible to implement a function that automatically shows a human friendly format for UDT (e.g. toString() in Java or ___repr__ in Python)?

Any pointers would be greatly appreciated.

Thank you.

Alessandro

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.