Helping fixing the code.

Hi Experts,
Help me fix the code below :

BEGIN
for i IN (SELECT owner, table_name, constraint_name
FROM dba_constraints
WHERE constraint_type ='R'
AND OWNER IN ('BO','CCRC', 'IA', 'MOMENTUM', 'ODS', 'PB', 'UFMS','UF_ODS', 'VSS', 'VSWMAUDIT', 'WFWMAUDIT', 'WMAUDIT', 'WORKFLOW')
AND status = 'ENABLED')
loop
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;
End;
/


When I run the code above I get,

BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 8

However, when I run the embeded Select statement, it runs successfully and I get 1650 rows.
Kamal AgnihotriAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Technically a different question.

I'm not where I can verify this but is it possible to have a constraint on a table in a different schema?  Check the constraint types and owners and table owners.

There is either a different owner or a constraint type that isn't against a table.  I can't look it up now but this should really be a different question.
0
 
slightwv (䄆 Netminder) Commented:
try changing:
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' disable constraint ' ||i.constraint_name;

To:
EXECUTE IMMEDIATE 'alter table ' || i.owner ||'.' ||i.table_name|| ' disable constraint ' ||i.constraint_name;
0
 
Kamal AgnihotriAuthor Commented:
Hi Slightwv,

Your sugestion worked, but when I make a slight change in the where clause, "WHERE constraint_type <>'R' " I get the same error.


SYS@ufms033>> BEGIN
  2  for i IN (SELECT owner, table_name, constraint_name
  3  FROM dba_constraints
  4  WHERE constraint_type <>'R'
  5  AND OWNER IN ('BO','CCRC', 'IA', 'MOMENTUM', 'ODS', 'PB', 'UFMS','UF_ODS', 'VSS', 'VSWMAUDIT',
'WFWMAUDIT', 'WMAUDIT', 'WORKFLOW')
  6  AND status = 'ENABLED')
  7  loop
  8  EXECUTE IMMEDIATE 'alter table '|| i.owner ||'.' ||i.table_name|| ' disable constraint ' ||i.co
nstraint_name;
  9  end loop i;
 10  END;
 11  /
BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 8

Thanks alot.

Sincerely,

Kamal
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.