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

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

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>