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

2 Responses to 'Describe User Record Types'

Subscribe to comments with RSS or TrackBack to 'Describe User Record Types'.

  1. “I’m still working through the metadata to find how to link those meaningless type names back to meaningful package specifications.”

    The ‘big’ number is the object_id. So you can track the object back to the package, but it isn’t necessarily consistent between dev/test/prod.

    SQL> CREATE OR REPLACE package t IS
      2    TYPE r_rec IS record (val varchar2(10), nnm NUMBER);
      3    TYPE t_rec IS TABLE OF r_rec;
      4    FUNCTION f RETURN t_rec pipelined;
      5  END;
      6  /

    Package created.

    SQL> SELECT type_name FROM user_types;
     
    TYPE_NAME
    ------------------------------
    SYS_PLSQL_16848_24_1
    SYS_PLSQL_16848_DUMMY_1
    SYS_PLSQL_16848_9_1
     
    SQL> SELECT object_id, object_name FROM user_objects WHERE object_name = 'T';
     
     OBJECT_ID OBJECT_NAME
    ---------- --------------------
         16848 T

    Gary

    23 Mar 09 at 3:40 pm

  2. Thanks, I hadn’t checked the OBJECT_ID relations on this yet. It was very interesting. The OBJECT_ID ties out to the package where the record types is defined, as found in the USER_TYPES view. The collection uses the same OBJECT_ID value in its TYPE_NAME and the record type number in the ELEM_TYPE_NAME in the USER_COLL_TYPES view.

    Also, the types aren’t created until you define a pipelined function. The auto generation of both collection and record types appears to be an undocumented feature. While dropping the pipelined functions doesn’t automatically remove the system generated type or collection type. Recreation of the object only updates the catalog stamp of the data type unless you make a change to the record or collection types. This does present the opportunity of using regular expressions to track back and identify pipelined collections and their base record types.

    maclochlainn

    24 Mar 09 at 12:22 am

Leave a Reply