Help using Base SAS procedures

Integrity constraints in Oracle DBMS

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Integrity constraints in Oracle DBMS

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


Accepted Solutions
Solution
‎02-01-2013 04:12 PM
Contributor
Posts: 69

Re: Integrity constraints in Oracle DBMS

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;

View solution in original post


All Replies
Super User
Posts: 5,438

Re: Integrity constraints in Oracle DBMS

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.

Data never sleeps
Solution
‎02-01-2013 04:12 PM
Contributor
Posts: 69

Re: Integrity constraints in Oracle DBMS

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 638 views
  • 3 likes
  • 3 in conversation