SQL & PL/SQL

Hello,

Ours is an E-Biz 11i environment on 9i database. Every night we're creating a break fix environment by cloning production. One of the tasks this process does is dropping and re-creating the database links in APPS and other schemas.

We've a post-clone SQL script to do the above, along with the other database tasks such as updating the profiles, cancelling the pending concurrent requests, recreating the database directories etc. Right now, there're a few individual SQLs that do the above. I'm consolidating them all in a single PL/SQL script. Within this, I've written a procedure to drop and re-create the links. It accepts the link name, target user/password it's connecting to and the host name as parameters and drops/re-creates the links.

As long as APPS calls this procedure, it works very well. Since, I can't connect to a different user from within the PL/SQL block, I tried to use 'alter session set current_schema = <username>' and try calling the above procedure, but I'm getting the ORA-1031 error. I tried even using the invoker rights (by defining the procedure with "authid current_user" clause) but still the same error.

Then I came to know about this DBMS_SYS_SQL and thought I got the solution I wanted when I read about the parse_as_user procedure. But still I've no luck. I tried to test the above from a PL/SQL block, as APPS. It works for the APPS schema and when it comes to the other schemas, problem still remains.

I tried to run it as SYS user as well. But again, it works well for the APPS schema and for the other schemas, it throws the same ORA-1031 error. I made sure that the other schemas have the execute privilege on DBMS_SYS_SQL package and it's qualified as "sys.dbms_sys_sql.parse_as_user" when it is called.

This really confuses me. When running this procedure as SYS, it can drop and re-create the links in APPS schema, buy why not in the other schemas ? Do the other schemas need any other privilege ?

Any help is greatly appreciated.

Thanks and regds.
Muthu