Home » SQL & PL/SQL » SQL & PL/SQL » how to check data type and length
how to check data type and length [message #214567] Wed, 17 January 2007 02:23 Go to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi all,
Can anyone solve my problem.

I want to create a pl/sql code which will check the foreign key column data type and referencing primary key column data type and report those names who have mismatch in either data type or data length.

plz tell me how to do it.


Thanks,
Karisma
Re: how to check data type and length [message #214578 is a reply to message #214567] Wed, 17 January 2007 03:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You'd be needing USER_CONS_COLUMNS, USER_CONSTRAINTS and USER_TAB_COLUMNS I suppose.
Re: how to check data type and length [message #214589 is a reply to message #214578] Wed, 17 January 2007 03:22 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
i have used all those views but still unable to do it.
Re:ORA-01001: invalid cursor [message #214590 is a reply to message #214578] Wed, 17 January 2007 03:24 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
is there any problem with the code:



Declare
len user_tab_columns.data_length%TYPE;
con1 user_constraints.constraint_name%TYPE;
col1 user_tab_columns.column_name%TYPE;
tab1 user_tab_columns.table_name%TYPE;
con2 user_constraints.constraint_name%TYPE;
col2 user_tab_columns.column_name%TYPE;
tab2 user_tab_columns.table_name%TYPE;


Cursor csr_col1 IS
select constraint_name,column_name,table_name from user_cons_columns where
constraint_name IN (Select a.constraint_name from user_constraints a,user_constraints b
where a.r_constraint_name=b.constraint_name and a.constraint_type='R');
Cursor csr_col2 IS
select constraint_name,column_name,table_name from user_cons_columns where
constraint_name IN (Select a.r_constraint_name from user_constraints a,user_constraints b
where a.r_constraint_name=b.constraint_name and a.constraint_type='R');

Begin

open csr_col1 ;
loop
fetch csr_col1 into con1,col1,tab1;
Exit when csr_col1%notfound;
open csr_col2 ;
loop
fetch csr_col2 into con2,col2,tab2;
Exit when csr_col2%notfound;



declare

cursor len_chk is select fk.c1 fkcol,pk.c2 pkcol,nullif(d1,d2) d_type,nullif(l1,l2) len
from (select a.data_type d1,a.data_length l1,a.column_name c1,a.table_name t1 from user_tab_columns a,user_cons_columns b

where a.table_name=b.table_name and a.column_name=b.column_name and b.constraint_name=con1) fk,(select a.data_type

d2,a.data_length l2,a.column_name c2,a.table_name t2 from user_tab_columns a,user_cons_columns b where

a.table_name=b.table_name and a.column_name=b.column_name and b.constraint_name=con2) pk
where fk.t1=pk.t2;
BEGIN
For len_rec IN len_chk LOOP
if len_rec.d_type=0 then
if len_rec.len=0 then
dbms_output.put_line('length matched');
else
dbms_output.put_line('length did not match for'||len_rec.fkcol);
end if;
else
dbms_output.put_line('data type did not match for'||len_rec.fkcol);
end if;

if csr_col1%isopen then close csr_col1;
end if;
if csr_col2%isopen then close csr_col2;
end if;
end loop;
END;
close csr_col1 ;
close csr_col2 ;

end loop;
end loop;
exception
when no_data_found then
raise_application_error(-20000,'zero rows fetched');

END;
/


[Updated on: Wed, 17 January 2007 05:48]

Report message to a moderator

Re: how to check data type and length [message #214611 is a reply to message #214590] Wed, 17 January 2007 04:45 Go to previous messageGo to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
why is your csr_col2 and csr_col1 definition same??
Re: getting constraint_name and r_constraint_name [message #214616 is a reply to message #214611] Wed, 17 January 2007 04:54 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
hi,
its not same.One is giving me constraint_name and another r_constraint_name.
please suggest
Thanks,
karisma
Re: getting constraint_name and r_constraint_name [message #214667 is a reply to message #214616] Wed, 17 January 2007 07:38 Go to previous messageGo to next message
raj_2005
Messages: 5
Registered: February 2006
Location: Pune
Junior Member

Hello ,

You can use below query ..
It would be useful for you
select a.constraint_name,a.table_name,c.column_name,c.data_type,c.data_length
from ALL_CONS_COLUMNS a ,user_constraints b,user_tab_columns c
where a.constraint_name=b.constraint_name
and a.table_name=b.table_name
and c.table_name=a.table_name
and b.constraint_type='R'
and b.constraint_name like '%FK'
and b.r_constraint_name like '%PK'

select a.constraint_name,a.table_name,c.column_name,c.data_type,c.data_length
from ALL_CONS_COLUMNS a ,user_constraints b,user_tab_columns c
where a.constraint_name=b.constraint_name
and a.table_name=b.table_name
and c.table_name=a.table_name
and b.constraint_type='P'
and b.constraint_name like '%PK'


Regards,
Raj
Re: getting constraint_name and r_constraint_name [message #214669 is a reply to message #214667] Wed, 17 January 2007 07:43 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
raj_2005 wrote on Wed, 17 January 2007 08:38


and b.constraint_name like '%FK'
and b.r_constraint_name like '%PK'



And what if the person who named the constraints did not name them ending in PK and FK?
Re: how to check data type and length [message #214692 is a reply to message #214567] Wed, 17 January 2007 10:42 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
Quote:
select case when rcl.data_type <> pcl.data_type then 'typediff'
when rcl.data_length<> pcl.data_length then 'lendiff'
when rcl.data_precision <> pcl.data_precision then 'precdiff' end as foo ,
cl.rowner,cl.rtn,cl.rcol, cl.refc,
cl.powner,cl.ptn,cl.pcol,cl.pukc from
( select conslst.rowner,conslst.rtn,refct.column_name rcol, conslst.refc,
conslst.powner,conslst.ptn,pukct.column_name pcol,conslst.pukc
from
(select rcons.owner rowner ,rcons.table_name rtn ,rcons.constraint_name refc ,
pcons.owner powner ,pcons.table_name ptn ,pcons.constraint_name pukc
from dba_constraints rcons , dba_constraints pcons
where rcons.owner='GRR'
and rcons.constraint_type='R'
and rcons.r_constraint_name=pcons.constraint_name
and rcons.r_owner=pcons.owner ) conslst ,
dba_cons_columns refct,
dba_cons_columns pukct
where refct.owner= conslst.rowner
and pukct.owner=conslst.powner
and refct.table_name=conslst.rtn
and pukct.table_name=conslst.ptn
and refct.constraint_name=conslst.refc
and pukct.constraint_name=conslst.pukc ) cl,
dba_tab_columns rcl, dba_tab_columns pcl
where length
or rcl.data_precision <> pcl.data_precision)

cl.rowner=rcl.owner
and cl.rtn=rcl.table_name
and cl.rcol=rcl.column_name
and cl.powner=pcl.owner
and cl.ptn=pcl.table_name
and cl.pcol=pcl.column_name
and ( rcl.data_type <> pcl.data_type
or rcl.data_length <> pcl.data_
====


Substitute GRR with your schema name .

Srini
Re: how to check data type and length [message #214693 is a reply to message #214567] Wed, 17 January 2007 10:47 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Quote:

Quote:

select case when rcl.data_type <> pcl.data_type then 'typediff'
when rcl.data_length<> pcl.data_length then 'lendiff'
when rcl.data_precision <> pcl.data_precision then 'precdiff' end as foo ,
cl.rowner,cl.rtn,cl.rcol, cl.refc,
cl.powner,cl.ptn,cl.pcol,cl.pukc from
( select conslst.rowner,conslst.rtn,refct.column_name rcol, conslst.refc,
conslst.powner,conslst.ptn,pukct.column_name pcol,conslst.pukc
from
(select rcons.owner rowner ,rcons.table_name rtn ,rcons.constraint_name refc ,
pcons.owner powner ,pcons.table_name ptn ,pcons.constraint_name pukc
from dba_constraints rcons , dba_constraints pcons
where rcons.owner='GRR'
and rcons.constraint_type='R'
and rcons.r_constraint_name=pcons.constraint_name
and rcons.r_owner=pcons.owner ) conslst ,
dba_cons_columns refct,
dba_cons_columns pukct
where refct.owner= conslst.rowner
and pukct.owner=conslst.powner
and refct.table_name=conslst.rtn
and pukct.table_name=conslst.ptn
and refct.constraint_name=conslst.refc
and pukct.constraint_name=conslst.pukc ) cl,
dba_tab_columns rcl, dba_tab_columns pcl
where
cl.rowner=rcl.owner
and cl.rtn=rcl.table_name
and cl.rcol=rcl.column_name
and cl.powner=pcl.owner
and cl.ptn=pcl.table_name
and cl.pcol=pcl.column_name
and ( rcl.data_type <> pcl.data_type
or rcl.data_length <> pcl.data_length
or rcl.data_precision <> pcl.data_precision)
/

Re: how to check data type and length [message #214780 is a reply to message #214693] Thu, 18 January 2007 01:10 Go to previous message
karismapanda
Messages: 58
Registered: January 2007
Member
thanks
Previous Topic: SET COMMANDS
Next Topic: SQL Query
Goto Forum:
  


Current Time: Fri Dec 09 05:47:10 CST 2016

Total time taken to generate the page: 0.06574 seconds