MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Describe User Defined Types

with one comment

I finally got around to putting a view together to see my user-defined types (UDT). These are object types, which may or may not have a return type. When they don’t have a return type, they effectively act as SQL schema-level record structures. You can also put them into varray and nested table collections, which I blogged about here.

Let’s say you created a SQL schema-level record type before you went to lunch, but forgot the specifics and somebody yanked the plug on your machine. How would you find it? Here’s a view to make finding it nice, easy, and pretty without expensive software.

1. Create a UDT of your own, like:

SQL> CREATE OR REPLACE TYPE item_full_titles IS OBJECT
  2  ( item_title    VARCHAR2(60)
  3  , item_subtitle VARCHAR2(60));
  4  /

2. Create the following SCHEMA_TYPE_ATTRIBUTES view:

CREATE OR REPLACE VIEW schema_type_attributes AS
SELECT   uta.type_name
,        uta.attr_name
,        uta.attr_no
,        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_type_attrs uta
ORDER BY uta.type_name;

3. You want to make the output easy to read from the command line when you don’t pay $995 for a Toad license from Quest Software. You can use the following SQL*Plus formatting query.

CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
TTITLE OFF
 
SET ECHO OFF
SET FEEDBACK OFF
SET NULL ''
SET PAGESIZE 999
SET PAUSE OFF
SET TERM ON
SET TIME OFF
SET TIMING OFF
SET VERIFY OFF
 
SET HEADING ON
TTITLE LEFT 'Composite Type ['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 "Position"
COL c3 FORMAT A30 HEADING "Attribute Name"
COL c4 FORMAT A30 HEADING "Attribute Data Type"
 
SELECT   sta.type_name c1
,        sta.attr_no c2
,        sta.attr_name c3
,        sta.attr_type_name c4
FROM     schema_type_attributes sta
ORDER BY sta.type_name
,        sta.attr_no;

You should see output like this:

Composite TYPE [ITEM_FULL_TITLES]
--------------------------------------------------------
POSITION Attribute Name                 Attribute DATA TYPE
-------- ------------------------------ ------------------------------
       1 ITEM_TITLE                     VARCHAR2(60)
       2 ITEM_SUBTITLE                  VARCHAR2(60)

Hope this helps a few folks, as always …

Written by maclochlainn

June 14th, 2009 at 5:52 pm

Posted in Uncategorized

One Response to 'Describe User Defined Types'

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

  1. This is brilliant! Thanks!!!!

    Jules

    11 Sep 14 at 12:23 pm

Leave a Reply