Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!50g2000hsm.googlegroups.com!not-for-mail
From:  xtremesparx@gmail.com
Newsgroups: comp.databases.oracle.server
Subject: PL/SQL: Run remote package with db_link as variable
Date: Wed, 10 Oct 2007 09:14:35 -0700
Organization: http://groups.google.com
Lines: 37
Message-ID: <1192032875.931672.131540@50g2000hsm.googlegroups.com>
NNTP-Posting-Host: 128.110.140.51
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1192032876 25536 127.0.0.1 (10 Oct 2007 16:14:36 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 10 Oct 2007 16:14:36 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: 50g2000hsm.googlegroups.com; posting-host=128.110.140.51;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.server:436178
X-Received-Date: Wed, 10 Oct 2007 12:14:36 EDT (text.usenetserver.com)

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.