Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is it possible to post PL/SQL-blocks for remote execution ??
Use something like this:
exec dbms_job.submit_at_b.world( 'begin .... end;' );
(read about dbms_job). It'll put the job in the queue on the other end of the link and execute it later. I've done this to do DDL through links. I write a pl/sql routine on the remote machine that can execute any sql statement and then execute something like:
exec dbms_job.submit_at_b.world(
'begin execute_immediate(''create table foo (x int)''); end' );
Hope this helps.
On Wed, 03 Dec 1997 23:44:30 GMT, Tollef.Melvold_at_lfk.mil.no (Tollef Melvold) wrote:
>
>I'm wondering if anyone know of a way to call PL/SQL-blocks remotely.
>
>What I'm after is the possibility to, with a DBMS_DEFER-call in
>database A post a DBMS_DEFER call in database B, and have this one
>execute a procedure in database B.
>
>The reason for this is that my procedure in database B Writes and
>Reads Package State, and therefor can't be called remotely.
>
>
>
>In details:
>What I'm trying today, but that fails is:
>
>Dbms_Defer.Add_Default_Dest( dblink => 'B.WORLD' );
>
>Dbms_Defer.Call(
> SCHEMA_NAME => 'USER',
> PACKAGE_NAME => 'My_Package',
> PROC_NAME => 'My_Proc',
> ARG_COUNT => 2);
>
>Dbms_Defer.Varchar2_Arg( value1_ );
>Dbms_Defer.Varchar2_Arg( value2_ );
>
>Dbms_Defer.Delete_Default_Dest( dblink => 'B.WORLD');
>
>
>The problem is that because 'My_Proc' writes/reads package state, this
>isn't possible to do across a dblink ( ORA-06574 ).
>
>Can I in any way post this entire PL/SQL-block across the dblink, to
>be executed by the job-queue on the B-side?
>
>
>Tollef Melvold
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 03 1997 - 00:00:00 CST