Re: PL/SQL: Run remote package with db_link as variable

From: <fitzjarrell_at_cox.net>
Date: Wed, 10 Oct 2007 10:32:04 -0700
Message-ID: <1192037524.056642.188860@v3g2000hsg.googlegroups.com>

On Oct 10, 12:25 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Oct 10, 11:14 am, xtremesp..._at_gmail.com wrote:
>
>
>
>
>
> > If this is the wrong group I'm sorry. Just let me know and I'll take
> > it there...
>
> > I am trying to run a package on several remote systems. As such, I'd
> > like to just loop through the list of db_link's and then run it for
> > each one. I am having a problem using a variable in the actual
> > package call. I've tried several things, but what I'm currently
> > trying is:
>
> > statement := 'package.procedure@'||dbaccess_info.db_link_name||'(var1,
> > var2)';
> > DBMS_OUTPUT.PUT_LINE(statement);
> > statement;
>
> > When trying to run the 'statement' line I've tried running things
> > like:
>
> > call statement;
> > exec statement;
> > execute immediate statement;
>
> > I know the literal string statement is correct but I can't get it to
> > actually run as a package call.
>
> > I also tried using variable substitution and got it to go fine if I
> > kept to running it through SQL Plus, but wasn't able to figure out how
> > to use it in an automated fashion inside the program...
>
> > I've got to believe there's a way to do it either way and I don't
> > really care which way it happens. I'm at my wits end right now
> > (although I'll keep looking). This is my first attempt at PL/SQL and
> > I've found it more challenging to pick up given my already limited
> > background (mainly VB, PHP).
>
> > Any suggestions of how to make either the string variable execute or
> > using variable substitution would be vastly appreciated, thanks.
>
> This:
>
> statement := 'package.procedure@'||
> dbaccess_info.db_link_name||'(var1,var2)';
>
> should be:
>
> statement := 'begin package.procedure@'||
> dbaccess_info.db_link_name||'(var1,var2); end;';
>
> and you should use execute immediate statement;, which will work. As
> an example:
>
> SQL> declare
> 2 stmt varchar2(200);
> 3 remote varchar2(20) := 'blorkflapple';
> 4 begin
> 5 stmt:= 'begin mytest.myproc@'||remote||'(1,1); end;';
> 6 dbms_output.put_line(stmt);
> 7 execute immediate stmt;
> 8 stmt:= 'begin mytest.myproc@'||remote||'(1,2); end;';
> 9 dbms_output.put_line(stmt);
> 10 execute immediate stmt;
> 11 stmt:= 'begin mytest.myproc@'||remote||'(2,1); end;';
> 12 dbms_output.put_line(stmt);
> 13 execute immediate stmt;
> 14 end;
> 15 /
> begin mytest.myproc_at_blorkflapple(1,1); end;
> 40
> begin mytest.myproc_at_blorkflapple(1,2); end;
> NOPE
> begin mytest.myproc_at_blorkflapple(2,1); end;
> 80
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

One additional note: using an actual database link will cause the output, if any, to be displayed on the remote server, unlike the example I posted, which was using the remote syntax on a local database (I was connected to blorkflapple [not the real name] at the time I was running the 'remote' package.procedure call). Using a db link to blorkflapple I get:

SQL> declare

  2          stmt varchar2(200);
  3          remote varchar2(12) := 'blorkflapple';
  4  begin
  5          stmt:= 'begin mytest.myproc@'||remote||'(1,1); end;';
  6          dbms_output.put_line(stmt);
  7          execute immediate stmt;
  8          stmt:= 'begin mytest.myproc@'||remote||'(1,2); end;';
  9          dbms_output.put_line(stmt);
 10          execute immediate stmt;
 11          stmt:= 'begin mytest.myproc@'||remote||'(2,1); end;';
 12          dbms_output.put_line(stmt);
 13          execute immediate stmt;

 14 end;
 15 /

begin mytest.myproc_at_blorkflapple(1,1); end;
begin mytest.myproc_at_blorkflapple(1,2); end;
begin mytest.myproc_at_blorkflapple(2,1); end;

PL/SQL procedure successfully completed.

SQL> The procedure successfully ran, but the output was generated and displayed elsewhere.

David Fitzjarrell Received on Wed Oct 10 2007 - 12:32:04 CDT