René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

September 25, 2005: On a better describe

I am a bit disappointed with describe. When I use it to describe a table (or view), it only reports column names, their types and if they have not null constraints. But I also want it to print the comments on a table and its columns. And, I'd appreciate if it told me which columns make a primary key and which tables are referenced by their foreign keys. It would be nice if describe told me, if the described thing is a table or a view. Lastly, I want to know the owner of the table (or view).
Because describe lacks all those little -but imho useful- features, I decided to write my own. It consists of two parts: a package and an SQL script. The script is basically a wrapper called from SQL*Plus and wrapps the call of the procedure in the package.
Here's the specification of the package:
create or replace package desc_table 
  authid current_user
as

  table_does_not_exist exception;
  pragma exception_init(table_does_not_exist, -20010);

  type check_t        is table of long;

  type col_t          is record (name varchar2(30), nullable boolean, datatype varchar2(106), checks check_t);
  type cols_t         is table   of col_t;

  type col_comment_t  is record (pos number, comment user_tab_comments.comments%type);
  type col_comments_t is table   of col_comment_t;

  type table_t        is record (own varchar2(30), nam varchar2(30));
  type tables_t       is table   of table_t;

  type char_to_number is table   of number(2) index by varchar2(30);

  type description    is record (tab           table_t,
                                 tab_type      user_tab_comments.table_type%type, -- 'TABLE', 'VIEW' ..?
                                 tab_comment   user_tab_comments.comments%type,
                                 cols          cols_t,
                                 col_comments  col_comments_t,
                                 pks           char_to_number, -- Position of primary keys
                                 parents       tables_t,
                                 children      tables_t);

  -- table_name: 61 chars maximum: 30 chars schema (optional), 1 char dot (optional), 30 chars username 
  function describe(table_name in varchar2) return description;

  function describe(tab        in table_t ) return description;

end desc_table;
/
Here's the package body:
create or replace package body desc_table as 

  function describe(table_name in varchar2) return description is 
    -- used for dbms_utility.name_resolve:
    util_context       number := 2;
    util_schema        varchar2(30);
    util_part1         varchar2(30);
    util_part2         varchar2(30);
    util_dblink        varchar2(128);
    util_part1_type    number;
    util_object_number number;

    tab                table_t;
  begin
    dbms_utility.name_resolve(table_name, util_context, util_schema, util_part1, util_part2, util_dblink, util_part1_type, util_object_number);

    tab.own := util_schema;
    tab.nam := util_part1;

    return describe(tab);

  exception
    when others then 
      case 
        when sqlcode = -6564 then 
        raise table_does_not_exist;
      else
        dbms_output.put_line('exception: ' || sqlerrm || '(' || sqlcode || ')' ); 
    end case;

  end describe;

  function describe(tab in table_t) return description is
    col_r         col_t;
    ret           description;
    v_table_name  varchar2(30);
    v_table_owner varchar2(30);
    col_pos            number;

  begin

    ret.tab          := tab;

    ret.cols         := cols_t        ();
    ret.col_comments := col_comments_t();
    ret.parents      := tables_t      ();
    ret.children     := tables_t      ();

    select comments,table_type into ret.tab_comment, ret.tab_type from all_tab_comments 
     where table_name = tab.nam and owner = tab.own;

    col_pos := 1;

    for r in (
      select
        t.column_name, t.data_type, t.data_length, t.data_precision, t.data_scale, t.nullable, c.comments
      from
        all_tab_cols t join all_col_comments c on 
          t.table_name  = c.table_name  and 
          t.column_name = c.column_name and
          t.owner       = c.owner
      where
        t.table_name = tab.nam and t.owner = tab.own
      order by
        column_id) loop

      col_r.name       := r.column_name;
      col_r.nullable   := case when r.nullable = 'Y' then true else false end;
      col_r.datatype   := r.data_type;
      col_r.checks     := check_t();

      if r.data_length is not null and r.data_precision is null then
        if r.data_type <> 'DATE' then
          col_r.datatype := col_r.datatype || '(' || r.data_length || ')';
        end if;
      end if;

      if r.data_precision is not null then
        col_r.datatype := col_r.datatype || '(' || r.data_precision;

        if r.data_scale is not null and r.data_scale > 0 then
          col_r.datatype := col_r.datatype || ',' || r.data_scale;
        end if;

        col_r.datatype := col_r.datatype || ')';
      end if;

      ret.cols.extend;
      ret.cols(ret.cols.count) := col_r;

      if r.comments is not null then
        ret.col_comments.extend;
        ret.col_comments(ret.col_comments.count).pos     := col_pos; 
        ret.col_comments(ret.col_comments.count).comment := r.comments;
      end if;
      
      col_pos := col_pos+1;
    end loop;

    -- finding constraints
    for r in (
      select
        r_owner, constraint_name, r_constraint_name, constraint_type, search_condition
      from
        all_constraints
      where
        table_name = tab.nam and owner = tab.own) loop

        if r.constraint_type = 'P' then
          for c in (
            select column_name, table_name, position
              from all_cons_columns
             where constraint_name = r.constraint_name) loop

            ret.pks(c.column_name) := c.position;
          end loop;

          select distinct /* distinct in case a table has two foreign keys to table */
            owner, table_name bulk collect into ret.children
          from
            all_constraints
          where
            r_constraint_name = r.constraint_name and
            owner             = tab.own;

        elsif r.constraint_type = 'R' then -- foreign key

          select owner, table_name into v_table_owner, v_table_name 
            from all_constraints 
           where constraint_name = r.r_constraint_name and owner = r.r_owner;

          ret.parents.extend;
          ret.parents(ret.parents.count).own := v_table_owner;
          ret.parents(ret.parents.count).nam := v_table_name;
        end if;

      end loop;

    return ret;

  end describe;

end;
/
Here's the wrapper SQL script:
set verify off

declare
  description desc_table.description;
  cols        desc_table.cols_t;
  cur_col_no  number;
  i    number;

begin

  description := desc_table.describe('&1');
  cols        := description.cols;

  dbms_output.new_line;
  dbms_output.put_line(' Describing ' || description.tab.own || '.' || description.tab.nam);
  dbms_output.put_line(' Type:      ' || description.tab_type);
  dbms_output.put_line(' Comment:   ' || description.tab_comment);

  dbms_output.put_line(' ------------------------------------------------------------');
  dbms_output.put_line(' Name                           Null?    Type              PK');
  dbms_output.put_line(' ------------------------------ -------- ----------------- --');

  cur_col_no := cols.first;
  while cur_col_no is not null loop
    dbms_output.put(' ');

    dbms_output.put(rpad(cols(cur_col_no).name,30));

    dbms_output.put(' ');

    if (cols(cur_col_no).nullable) then
      dbms_output.put('         ');        
    else
      dbms_output.put('NOT NULL ');
    end if;

    dbms_output.put(rpad(cols(cur_col_no).datatype, 17));

    if description.pks.exists(cols(cur_col_no).name) then
      dbms_output.put(lpad(to_char(description.pks(cols(cur_col_no).name)),3)); 
    else
      dbms_output.put('   ');
    end if;
   
    dbms_output.new_line();
    cur_col_no:=cols.next(cur_col_no);
  end loop;

  if description.parents.count > 0 then
    dbms_output.new_line;
    dbms_output.put_line(' Parents: ');

    for parent_no in 1 .. description.parents.count loop
      dbms_output.put_line('  ' || description.parents(parent_no).own || '.' || description.parents(parent_no).nam);
    end loop;

    dbms_output.new_line;
  end if;

  if description.children.count > 0 then
    dbms_output.new_line;
    dbms_output.put_line(' Children: ');

    for child_no in 1 .. description.children.count loop
      dbms_output.put_line('  ' || description.children(child_no).own || '.' || description.children(child_no).nam);
    end loop;

    dbms_output.new_line;
  end if;

  if description.col_comments.count > 0 then
    dbms_output.new_line;
    dbms_output.put_line(' Column comments:');
    dbms_output.put_line(' -----------------');
    dbms_output.new_line;
  
    for cur_col_idx /* not pos ! */ in 1 .. description.col_comments.count loop
      dbms_output.put(' ');
  
      dbms_output.put_line(cols(description.col_comments(cur_col_idx).pos).name || ': ' || description.col_comments(cur_col_idx).comment);
      dbms_output.new_line;
     
      cur_col_no:=cols.next(cur_col_no);
    end loop;
  end if;

  exception
    when desc_table.table_does_not_exist then
      dbms_output.put_line('no such table: &1');

    when others then
      dbms_output.put_line('unknown exception, ' || sqlerrm || '(' || sqlcode || ')');
end;
/
This wrapper script should be saved into a directory pointed at by SQLPATH or into the current directory. For the following experiments, I named the script desc.sql.
I create two tables which will then be used to be described:
create table desc_test_parent (
  pk_1    number,
  pk_2    number,
  col_1   varchar2(30),
  col_2   date,
  col_3   number,
  primary key (pk_1, pk_2)
);

create table desc_test_child (
  fk_1,
  fk_2,
  col_4   blob,
  col_5   raw(50),
  foreign key (fk_1, fk_2) references desc_test_parent(pk_1, pk_2)
);
Some comments are created on both the tables and some columns:
comment on table  desc_test_parent is 'Parent table';
comment on table  desc_test_child is 'Child table';

comment on column desc_test_parent.pk_1  is 'first part of primary key';
comment on column desc_test_parent.pk_2  is 'second part of primary key';
comment on column desc_test_parent.col_2 is 'Just an idle comment on col_2';

comment on column desc_test_child.fk_1  is 'first part of foreign key';
comment on column desc_test_child.fk_2  is 'second part of foreign key';
comment on column desc_test_child.col_5 is 'Some idle comment on col_5';
In order to be able to describe tables in all schemas, the user executing the package needs the select any table privilege explicitely granted:
connect / as sysdba

grant select any table to rene;
Let's use the thing.
@desc desc_test_parent
Here's the output. As can be seen, it truthfully reports the children (being only RENE.DESC_TEST_CHILD) for this table. Also, all comments are displayed. The column PK displays which columns make the primary key.
 Describing RENE.DESC_TEST_PARENT
 Type:      TABLE
 Comment:   Parent table
 ------------------------------------------------------------
 Name                           Null?    Type              PK
 ------------------------------ -------- ----------------- --
 PK_1                           NOT NULL NUMBER(22)         1
 PK_2                           NOT NULL NUMBER(22)         2
 COL_1                                   VARCHAR2(30)
 COL_2                                   DATE
 COL_3                                   NUMBER(22)

 Children:
  RENE.DESC_TEST_CHILD


 Column comments:
 -----------------

 PK_1: first part of primary key

 PK_2: second part of primary key

 COL_2: Just an idle comment on col_2
Now, describing desc_test_child:
@desc desc_test_child
 Describing RENE.DESC_TEST_CHILD
 Type:      TABLE
 Comment:   Child table
 ------------------------------------------------------------
 Name                           Null?    Type              PK
 ------------------------------ -------- ----------------- --
 FK_1                                    NUMBER(22)
 FK_2                                    NUMBER(22)
 COL_4                                   BLOB(4000)
 COL_5                                   RAW(50)

 Parents:
  RENE.DESC_TEST_PARENT


 Column comments:
 -----------------

 FK_1: first part of foreign key

 FK_2: second part of foreign key

 COL_5: Some idle comment on col_5
Describing a table in another schema:
@desc hr.jobs
 Describing HR.JOBS
 Type:      TABLE
 Comment:   jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.
 ------------------------------------------------------------
 Name                           Null?    Type              PK
 ------------------------------ -------- ----------------- --
 JOB_ID                         NOT NULL VARCHAR2(10)       1
 JOB_TITLE                      NOT NULL VARCHAR2(35)
 MIN_SALARY                              NUMBER(6)
 MAX_SALARY                              NUMBER(6)

 Children:
  HR.JOB_HISTORY
  HR.EMPLOYEES


 Column comments:
 -----------------

 JOB_ID: Primary key of jobs table.

 JOB_TITLE: A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT

 MIN_SALARY: Minimum salary for a job title.

 MAX_SALARY: Maximum salary for a job title
Finally, what happens if a non existing table is described:
@desc table_no_no
no such table: desc_no_no

Update of November 12, 2005

Janus Christensen sent me the following email:
Hello René,

I've had a look at your "better describe" package and SQL*Plus script
(http://www.adp-gmbh.ch/blog/2005/september/25.html) and have started using
them. I have a few suggestions for enhancements for the code in the package.


1. I've altered the package specification declaration to

    create or replace package desc_table authid current_user as

this way I can stick it in a schema and grant execute to public (and a public
synonym) and use from any schema.


2. The following SQL-statement:

select
  t.column_name, t.data_type, t.data_length, t.data_precision, t.data_scale, t.nullable, c.comments
from
  all_tab_cols t join all_col_comments c on t.table_name = c.table_name and t.column_name = c.column_name
where
  t.table_name = tab.nam and t.owner = tab.own
order by
  column_id

I have changed to:

select
  t.column_name, t.data_type, t.data_length, t.data_precision, t.data_scale, t.nullable, c.comments
  from
    all_tab_columns t inner join all_col_comments c
                              on t.table_name = c.table_name and
                                 t.column_name = c.column_name and
                                 t.owner = c.owner
where
  t.table_name = tab.nam and t.owner = tab.own
order by
  column_id) loop

so that it now also checks for the owner of the table/column in
ALL_COL_COLUMNS, otherwise the SQL will include columns with the same name and
table name from different schemas.


3. Similarly to #2 above I have added a check for the owner in the BULK COLLECT that finds a table's children:

select distinct /* distinct in case a table has two foreign keys to table */
  owner, table_name bulk collect into ret.children
from
  all_constraints
where
  r_constraint_name = r.constraint_name
and owner = tab.own

otherwise it found tables in other schemas that weren't related to the table
being described.
I want to thank Janus for sending me his suggestions. As always, I am very happy to receive feedback, especially if it is improving something I have made.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.