|
SQL>
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 for z in ( select a.index_name, a.uniqueness from user_indexes a where a.table_name = upper(p_tname) and index_type = 'NORMAL' )
5 loop
6 dbms_output.put( rpad(z.index_name,31) ||z.uniqueness );
7 for y in ( select decode(column_position,1,'(',', ')|| column_name column_name
8 from user_ind_columns b
9 where b.index_name = z.index_name
10 order by column_position )
11 loop
12 dbms_output.put( y.column_name );
13 end loop;
14 dbms_output.put_line( ')' || ' ' );
15 end loop;
16
17 end;
18 /
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
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL>
|