MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Describe User Record Types

with 2 comments

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.

Written by maclochlainn

March 22nd, 2009 at 11:24 pm

Posted in Oracle,pl/sql,sql