set cursor for dropping constraints
I want to create a function that would drop constraints and return a boolean if it was done.
I'd like to pass in the schema name and owner ...I've tried various ways and seem to be getting messed up on the cursor ...
the errors I am getting are about the select statement. PLS-00103 encountered the Select when expecting one of the following :
We're building a test environment and will need to do this many many times. I know this isn't new to dbas, but when I do searches
I just can't seem to find something similar that meets our needs. I just want it to run in a script and the schema and table name
get passed in... all the constraints are found and disabled.
Thank you.
------ PASS in the schema name, owner
declare
cursor cur (schema_in varchar2(20), table_name_in varchar2)
select owner, table_name, constraint_name
from dba_constraints
where owner =schema_in
and table_name =table_name_in
order by table_name, constraint_name;
----- use those values to do the drop looping until they are all done....
begin
for v in cur(&schema_name, &a_table) Loop
execute immediate'Alter Table' || owner || '.' Table_name || 'Disable_constraint ' || constaint_name using
v.owner, v.table_name, v.constraint_name;
end loop;
close cur;
return (true);
end dropconstraints