asked on

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.

slightwv (䄆 Netminder)

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;

Kamal Agnihotri

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial