Switch user in pl/sql code block in sqlplus sessions

Question

Hi,

I have a pl/sql script that I run from a sqlplus session as 'sysdba' .

During the execution of the script I would like to switch to another user so I can do some db link creation for that user. After this is done the script should return to 'sysdba' for some finishing up.

I tried to use the following:

BEGIN

<do some stuff as sysdba>

execute immediate 'connect ' || in_owner || '/' || v_password;

<create db link for user>

<switch back to sysdba>
END;

However it seems that 'connect' is a command that can not be run with 'execute immediate'.

Any idea how to accomplish this ?

Thanks!

Answer 1

I'm not aware of how you'd do this in Orcle but the simple solution is to put it in your SQL script rather than a PL/SQL block.

BEGIN

<do some stuff as sysdba>

END;

connect scott/tiger;

<create db link for user>

<switch back to sysdba>

BEGIN

<do other stuff>

END;

EDIT: Based on your comment below I have another solution for you. Still use an SQL script with some PLSQL rather than a pure PLSQL script. This time, dynamically generate an SQL script to create the links then run it. The complication may be on how to figure out the password for each user.

BEGIN
<do some stuff as sysdba>
END;

set serverout on;
spool create_links.sql
BEGIN
    for <loop over each user> loop
        dbms_output.put_line('connect ' || user || '/' || pass ';');
        dbms_output.put_line('<create db link for user>;');
    end loop;
END;
spool off;

@@create_links.sql;

<switch back to sysdba>

BEGIN

<do other stuff>

END;

Finally, if this doesn't work, you can write a procedure that dynamically creates the trigger using "AUTHID CURRENT_USER". This makes a procedure run as the caller rather than the owner. See here for how to use the AUTHID keyword when defining procedures.

Answer 2

Could you change to a different schema before creating the dblink?

ALTER SESSION SET CURRENT_SCHEMA = in_owner;
<create db link for user>
ALTER SESSION SET CURRENT_SCHEMA = 'sysdba';