Describe User Record Types
Gary Myers made a comment on the blog that got me thinking about how to look up user defined types (UDTs). Like those UDTs that you define to leverage pipelined functions and procedures. It became more interesting while considering how Oracle Object Types act as SQL record types. At least, that’s their default behavior when you don’t qualify a return type of self (that’s this in Oracle objects for those who write in any other object-oriented programming language).
The following query creates a view of data types in your user schema. It is fairly straightforward and written to let you deploy the view in any schema. You’ll need to make changes if you’d like it work against the ALL or DBA views.
CREATE OR REPLACE VIEW schema_types AS SELECT ut.type_name AS type_name , uta.attr_no AS position_id , uta.attr_name AS attribute_name , DECODE(uta.attr_type_name , 'BFILE' ,'BINARY FILE LOB' , 'BINARY_FLOAT' ,uta.attr_type_name , 'BINARY_DOUBLE',uta.attr_type_name , 'BLOB' ,uta.attr_type_name , 'CLOB' ,uta.attr_type_name , 'CHAR' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'DATE' ,uta.attr_type_name , 'FLOAT' ,uta.attr_type_name , 'LONG RAW' ,uta.attr_type_name , 'NCHAR' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'NVARCHAR2' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'NUMBER' ,DECODE(NVL(uta.precision||uta.scale,0) , 0,uta.attr_type_name , DECODE(NVL(uta.scale,0),0 , uta.attr_type_name||'('||uta.precision||')' , uta.attr_type_name||'('||uta.precision||','|| uta.scale||')')) , 'RAW' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'VARCHAR' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'VARCHAR2' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'TIMESTAMP' , uta.attr_type_name,uta.attr_type_name) AS attr_type_name FROM user_types ut, user_type_attrs uta WHERE ut.type_name = uta.type_name ORDER BY ut.type_name, uta.attr_no; |
You can query and format the view as follows:
CLEAR BREAKS CLEAR COLUMNS CLEAR COMPUTES TTITLE OFF SET ECHO ON SET FEEDBACK OFF SET NULL '' SET PAGESIZE 999 SET PAUSE OFF SET TERM ON SET TIME OFF SET TIMING OFF SET VERIFY OFF ACCEPT INPUT PROMPT "Enter type name: " SET HEADING ON TTITLE LEFT o1 SKIP 1 - '--------------------------------------------------------' SKIP 1 CLEAR COLUMNS CLEAR BREAKS BREAK ON REPORT BREAK ON c1 SKIP PAGE COL c1 NEW_VALUE o1 NOPRINT COL c2 FORMAT 999 HEADING "ID" COL c3 FORMAT A32 HEADING "Attribute Name" COL c4 FORMAT A33 HEADING "Attribute Type" SELECT st.type_name c1 , st.position_id c2 , st.attribute_name c3 , st.attr_type_name c4 FROM schema_types st WHERE st.type_name LIKE UPPER('&input')||'%' ORDER BY st.type_name , st.position_id; |
Here’s a sample output for an object type named common_lookup_object:
COMMON_LOOKUP_OBJECT -------------------------------------------------------- ID Attribute Name Attribute TYPE ---- -------------------------------- ------------------ 1 COMMON_LOOKUP_ID NUMBER 2 COMMON_LOOKUP_TYPE VARCHAR2(30) 3 COMMON_LOOKUP_MEANING VARCHAR2(255) |
It certainly makes the point that a named data type is most convenient. I’m still working through the metadata to find how to link those meaningless type names back to meaningful package specifications. If you know, let me know in a comment. Hope this helps somebody.
Subscribe via RSS