oracle 通过数据库链接立即执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1119306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
execute immediate over database link
提问by gabor
Is it possible to execute dynamic PL/SQL on a remote database via a databse link?
是否可以通过数据库链接在远程数据库上执行动态 PL/SQL？
I'm looking for something like:
我正在寻找类似的东西：
l_stmt := 'begin null; end;';
execute immediate l_stmt@dblink;
The syntax above is obviously wrong, I get PLS-00201: identifier 'L_STMT@DBLINK' must be declared.
上面的语法显然是错误的，我得到PLS-00201: identifier 'L_STMT@DBLINK' must be denied。
It is possible to create a procedure remotely and then execute it. Is there a way to execute code without creating a remote procedure?
可以远程创建一个过程然后执行它。有没有办法在不创建远程过程的情况下执行代码？
EDIT: I'm trying to work around passing a type over DB link. A remote procedure requires a parameter of type t_id_tab which is defined on the remote DB as
编辑：我正在尝试解决通过数据库链接传递类型的问题。远程过程需要一个类型为 t_id_tab 的参数，该参数在远程 DB 上定义为
CREATE OR REPLACE TYPE T_ID_TAB AS TABLE OF NUMBER(12)
回答by Dave Costa
You can execute arbitary code on the remote database by calling the DBMS_SQL package there.
您可以通过调用那里的 DBMS_SQL 包在远程数据库上执行任意代码。
Sample:
样本：
set serveroutput on
create or replace synonym remote_dbms_sql for dbms_sql@core;
declare
c number;
l_global_name varchar2(200);
begin
c := remote_dbms_sql.open_cursor();
remote_dbms_sql.parse( c, 'select global_name from global_name', dbms_sql.native );
remote_dbms_sql.define_column( c, 1, l_global_name, 200 );
dbms_output.put_line( remote_dbms_sql.execute_and_fetch( c ) );
remote_dbms_sql.column_value( c, 1, l_global_name );
dbms_output.put_line( l_global_name );
remote_dbms_sql.close_cursor( c );
end;
/
Note that the reference to DBMS_SQL.NATIVE is local, not remote. You can't reference remote package constants, but presumably the actual value of this constant is the same in both databases.
请注意，对 DBMS_SQL.NATIVE 的引用是本地的，而不是远程的。您不能引用远程包常量，但推测该常量的实际值在两个数据库中是相同的。
回答by David Aldridge
I would think that you would just qualify the object names in the procedure, rather than qualifying the procedure itself.
我认为您只会限定过程中的对象名称，而不是限定过程本身。
回答by prayash khadka
You are missing execute in above example. Please see below:
你在上面的例子中缺少执行。请参阅以下内容：
ret := DBMS_SQL.EXECUTE(c);
回答by FerranB
Have you tried to create the array on a package instead of a type? I mean:
您是否尝试在包而不是类型上创建数组？我的意思是：
CREATE OR REPLACE PACKAGE the_package AS
TYPE T_ID_TAB AS TABLE OF NUMBER(12);
END the_package;
May be, with this way works, I've not tried...
可能是，这种方式有效，我还没有尝试过......
