1

I'm writing a script to check SQL against a standards document before it's implemented. I'm currently trying to write a script that compares the Primary and Foreign key's data type and precision. I know I need to use:

DBA_TAB_COlUMNS DBA_CONSTRAINTS DBA_CONS_COLUMNS

But I can't seem to join them in a way that's going to compare the datatypes and return a result if there's a difference.

0

I am sure this can be optimized but here is to experiment,

with parent as (select --+ no_merge(dc) no_merge(dccr) no_merge(dtcr) 
distinct dc.r_owner ref_owner,  dc.r_constraint_name,dtcr.column_name
,case when dtcr.NULLABLE = 'N' then 'NOT NULL ' end ||
  case when dtcr.DATA_TYPE in ('CHAR','VARCHAR2') then dtcr.DATA_TYPE || ' (' || dtcr.DATA_LENGTH || ')' 
     when dtcr.DATA_TYPE = 'DATE' then 'DATE' 
     when dtcr.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || dtcr.DATA_SCALE || ')' 
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is null then 'NUMBER'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is not null then 'NUMBER (' || dtcr.DATA_PRECISION ||',' || NVL(dtcr.DATA_SCALE,'0') || ')'
end c_dt
from dba_constraints dc
join dba_cons_columns dccr on (dccr.owner = dc.r_owner and dccr.constraint_name = dc.r_constraint_name)
join dba_tab_columns dtcr  on (dccr.owner = dtcr.owner and dccr.column_name = dtcr.column_name and dccr.table_name = dtcr.table_name)
where dc.constraint_type = 'R' and dc.r_owner = 'SCHEMA_OWNER')
,child as (select --+ no_merge(dc) no_merge(dccr) no_merge(dtcr) 
distinct dc.owner ref_owner,  dc.constraint_name, dc.r_constraint_name, dtcr.column_name, dtcr.table_name
,case when dtcr.NULLABLE = 'N' then 'NOT NULL ' end ||
  case when dtcr.DATA_TYPE in ('CHAR','VARCHAR2') then dtcr.DATA_TYPE || ' (' || dtcr.DATA_LENGTH || ')' 
     when dtcr.DATA_TYPE = 'DATE' then 'DATE' 
     when dtcr.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || dtcr.DATA_SCALE || ')' 
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is null then 'NUMBER'
     when dtcr.DATA_TYPE = 'NUMBER' and dtcr.DATA_PRECISION is not null then 'NUMBER (' || dtcr.DATA_PRECISION ||',' || NVL(dtcr.DATA_SCALE,'0') || ')'
end c_dt
from dba_constraints dc
join dba_cons_columns dccr on (dccr.owner = dc.owner and dccr.constraint_name = dc.constraint_name)
join dba_tab_columns dtcr  on (dccr.owner = dtcr.owner and dccr.column_name = dtcr.column_name and dccr.table_name = dtcr.table_name)
where dc.constraint_type = 'R' )
select parent.ref_owner, parent.r_constraint_name ref_constraint, parent.column_name ref_col, parent.c_dt parent_col_datatype
,child.ref_owner child_owner, child.table_name child_table, child.r_constraint_name child_constraint, child.column_name child_col, child.c_dt child_col_datatype
from parent join child on (parent.r_constraint_name = child.r_constraint_name) order by 1,2,3;
  • Hi, thanks for this. I've just created a parent and child table and made 3 foreign keys from child to parent one with the correct type/precision, one with the correct type but wrong precision and one with neither and the script is still returning no rows. what result should I be expecting? will it select the offending column name? thanks again. – Michael Oddie Apr 3 '17 at 11:01
  • You probably want to take this sql and convert it to user_ views instead of dba_. Thus you will eliminate tables from other schema. – Raj Apr 3 '17 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.