|
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>
|