Output report for user_tab_columns : user_tab_columns « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. user_tab_columns

SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );

Table created.

SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

1 row created.

SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

1 row created.

SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

1 row created.

SQL>
SQL> create or replace procedure desc_table( p_tname in varchar2 ) AUTHID CURRENT_USER as
  2  begin
  3      dbms_output.put_line(p_tname );
  4      dbms_output.new_line;
  5
  6      for x in ( select column_name,data_type,substr(
  7               decode( data_type,'NUMBER', decode( data_precision, NULL, NULL,
  8                           '('||data_precision||','||data_scale||')' ),
  9                        data_length),1,11) data_length,
 10               decode( nullable,'Y','null','not null') nullable
 11          from user_tab_columns
 12         where table_name = upper(p_tname)
 13         order by column_id )
 14      loop
 15          dbms_output.put_line( rpad(x.column_name,31) ||
 16                                rpad(x.data_type,20)    ||
 17                                rpad(x.data_length,11) ||
 18                                x.nullable );
 19      end loop;
 20  end;
 21  /

Procedure created.

SQL>
SQL> grant execute on desc_table to public
  2  /

Grant succeeded.

SQL>
SQL> set serveroutput on format wrapped
SQL> exec desc_table( 'dept' )
dept

DEPTNO                         NUMBER              (2,0)      null
DNAME                          VARCHAR2            14         null
LOC                            VARCHAR2            13         null

PL/SQL procedure successfully completed.

SQL>
SQL> drop table dept;

Table dropped.

SQL>
SQL>
SQL>