Integrity constraints in Oracle DBMS

kemot25 — Fri, 01 Feb 2013 10:34:28 GMT

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

Re: Integrity constraints in Oracle DBMS

LinusH — Fri, 01 Feb 2013 13:10:19 GMT

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.

Re: Integrity constraints in Oracle DBMS

bentleyj1 — Fri, 01 Feb 2013 21:12:24 GMT

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;

topic Re: Integrity constraints in Oracle DBMS in SAS Procedures

Integrity constraints in Oracle DBMS

Re: Integrity constraints in Oracle DBMS

Re: Integrity constraints in Oracle DBMS