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
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.