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

From: <fitzjarrell_at_cox.net>
Date: Wed, 10 Oct 2007 10:25:32 -0700
Message-ID: <1192037132.239786.191630@d55g2000hsg.googlegroups.com>

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 Received on Wed Oct 10 2007 - 12:25:32 CDT