通过数据库链接立即执行 [英] execute immediate over database link
问题描述
是否可以通过数据库链接在远程数据库上执行动态PL/SQL?
Is it possible to execute dynamic PL/SQL on a remote database via a databse link?
我正在寻找类似的东西:
I'm looking for something like:
l_stmt := 'begin null; end;';
execute immediate l_stmt@dblink;
上面的语法显然是错误的,我得到 PLS-00201:必须声明标识符'L_STMT @ DBLINK'.
The syntax above is obviously wrong, I get PLS-00201: identifier 'L_STMT@DBLINK' must be declared.
可以远程创建一个过程,然后执行它.是否可以在不创建远程过程的情况下执行代码?
It is possible to create a procedure remotely and then execute it. Is there a way to execute code without creating a remote procedure?
我正在尝试解决通过数据库链接传递类型的问题.远程过程需要一个类型为t_id_tab的参数,该参数在远程数据库上定义为
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
CREATE OR REPLACE TYPE T_ID_TAB AS TABLE OF NUMBER(12)
推荐答案
您可以通过在远程数据库中调用DBMS_SQL程序包在远程数据库上执行任意代码.
You can execute arbitary code on the remote database by calling the DBMS_SQL package there.
示例:
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;
/
请注意,对DBMS_SQL.NATIVE的引用是本地的,而不是远程的.您不能引用远程程序包常量,但是大概两个数据库中该常量的实际值是相同的.
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.
这篇关于通过数据库链接立即执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!