MacLochlainns Weblog

I demonstrated a number of SQL approaches to reading object types in Appendix B of the Oracle Database 12c PL/SQL Programming book. For example, the easiest one to construct and return the results from a TO_STRING member function uses the TREAT function:

SELECT TREAT(base_t() AS base_t).to_string() AS "Text"
FROM   dual;

However, it seems that I could have provided one more. Here’s an example of how you can test the construction of an object type and how you can return its attributes with a query. It’s important to note that there’s a natural problem with this syntax when you increment a sequence inside the object type. The problem is that it double increments the counter for the sequence.

SELECT *
FROM   TABLE(SELECT CAST(COLLECT(base_t()) AS base_t_tab)
             FROM dual);

The syntax for the COLLECT function requires that you put it inside a SELECT-list. Then, the CAST function converts a single instance of the BASE_T object type to a one element BASE_T_TAB collection. Finally, the TABLE function returns a single row from the BASE_T_TAB collection.

You can find a more complete article covering column substitutability and object types and subtypes on the ToadWorld site. I think it helps clear up how you can effectively write PL/SQL types and subtypes for persistent object type columns.