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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.