在sqlplus会话中的pl/sql代码块中切换用户 [英] Switch user in pl/sql code block in sqlplus sessions

问题描述

我有一个从sqlplus会话中以'sysdba'运行的pl/sql脚本.

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

在执行脚本期间，我想切换到另一个用户，以便可以为该用户创建一些数据库链接.完成此操作后，脚本应返回到"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.

我尝试使用以下内容:

BEGIN

<do some stuff as sysdba>

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

<create db link for user>

<switch back to sysdba>
END;

但是，似乎"connect"是无法与"execute立即执行"一起运行的命令.

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

有什么想法要做到这一点吗?

Any idea how to accomplish this ?

谢谢！

推荐答案

我不知道您如何在Orcle中执行此操作，但简单的解决方案是将其放入SQL脚本而不是PL/SQL块中

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;

根据您在下面的评论，我为您提供了另一种解决方案. 仍然使用带有某些PLSQL的SQL脚本，而不是纯PLSQL脚本. 这次，动态生成一个SQL脚本来创建链接，然后运行它. 麻烦之处可能在于如何确定每个用户的密码.

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;

最后，如果这不起作用，则可以编写一个过程来使用"AUTHID CURRENT_USER"动态创建触发器.这使过程以调用者而不是所有者的身份运行.有关使用方法，请参见此处定义过程时使用AUTHID关键字.

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.

这篇关于在sqlplus会话中的pl/sql代码块中切换用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！