Help me fix the code
When I execute teh code below, I get error listed 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.owner ||'.' ||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;
END;
/
BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 8
However, when I run the embeded SQL Statement it returns 744 rows.
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.owner ||'.' ||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;
END;
/
BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 8
However, when I run the embeded SQL Statement it returns 744 rows.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am running the code logged in as sys. The above code does not work for me. It gives yhe error listed above.
However, a similar code, listed below works.
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.owner ||'.' ||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;
End;
/
The only difference between the two is the WHERE CONSTRAINT_TYPE <> 'R'