Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calling PL/SQL Procedure In Another Schema Gives Unexpected Result

734536Jan 11 2011 — edited Jan 13 2011
I have a SQL Script that does this:
conn pnr/<password for user pnr>;
set serveroutput on;
exec vms.disable_all_fk_constraints;
SELECT owner, constraint_name, status FROM user_constraints WHERE constraint_type = 'R';

and the disable_all_fk_constraints procedure that is owned by user 'vms' is defined as:
create or replace
procedure disable_all_fk_constraints is
v_sql VARCHAR2(4000);
begin
dbms_output.put_line('Disabling all referential integrity constraints.');
for rec in (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type='R') loop
dbms_output.put_line('Disabling constraint ' || rec.constraint_name || ' from ' || rec.table_name || '.');
v_sql := 'ALTER TABLE ' || rec.table_name || ' DISABLE CONSTRAINT ' || rec.constraint_name;
execute immediate(v_sql);
end loop;
end;

When I run the SQL script, the call to vms.disable_all_fk_constraints disables the FK constrains in the 'vms' schema, whereas I wanted it to disable the FK constraints in the 'pnr' schema (the invoker of the procedure). I know that I could make this work by copying the disable_all_fk_constraints procedure to the 'pnr' schema and calling it as "+exec disable_all_fk_constraints;+" from within the SQL script but I want to avoid having to duplicate the PL/SQL procedure in each schema that uses it.

What can I do?

Thank you
This post has been answered by Frank Kulash on Jan 11 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 10 2011
Added on Jan 11 2011
3 comments
4,016 views