Integrity constraints in Oracle DBMS

[kemot25]

Hello,

How to list integrity constraints of table from Oracle library?

DESCRIBE TABLE and DESCRIBE TABLE CONSTRAINTS or PROC DATASETS and CONTENTS does not work.

Thanks

[LinusH]

The SAS/ACCESS engines just support a subset of source database attributes.

Perhaps you need to use SQL pass-thru for having the required attributes visible for you.

[bentleyj1 · Accepted solution]

You’ll have to use the Oracle system tables to make this happen.  The system tables contain subject-specific metadata about all tables so in this case you canuse the sys.all_constraints table.  But Oracle keeps user-id specific hidden copies of these tables.  So you can use the table in your own schema named user_constraints.

You’ll want to google something like ‘Oracle System Tables’ to learn what’s in each table.  Here's a starting place-- http://www.techonthenet.com/oracle/sys_tables/

This code works for me.  Pulling the results set directly into a SAS data set doesn’t want to work so we first create a temporary Oracle table and then unload that into a data set where we can view or print the data.  We list the fields in the user_constraints table because the field SEARCH_CONDITION is a LONG data type and throws an error when it is selected into a new table .

proc sql;

      connect to oracle as orc (path='<alias for the db spec>'  user="&_hemidbuser" pass="&_hemidbpwd" readbuff=10000);

      ** Create an Oracle table. ;

      execute (

            create table temp_constraint_data as

                  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER

                            , R_CONSTRAINT_NAME

                            , DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED

                            , GENERATED, BAD, RELY            

                            , LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED   

                  from user_constraints

                  where table_name='EMPLOYEE'

            ) by orc;

      ** Download the Oracle table into a SAS data set. ;

      create table temp_constraint_data as

            select *

            from connection to orc

                  (select *

                  from temp_constraint_data);

      ** Drop the Oracle table and disconnect. ;

      execute (drop table temp_constraint_data) by orc;

      disconnect from orc;

quit;