Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!d55g2000hsg.googlegroups.com!not-for-mail From: "fitzjarrell@cox.net" Newsgroups: comp.databases.oracle.server Subject: Re: PL/SQL: Run remote package with db_link as variable Date: Wed, 10 Oct 2007 10:25:32 -0700 Organization: http://groups.google.com Lines: 80 Message-ID: <1192037132.239786.191630@d55g2000hsg.googlegroups.com> References: <1192032875.931672.131540@50g2000hsm.googlegroups.com> NNTP-Posting-Host: 138.32.32.166 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1192037132 7263 127.0.0.1 (10 Oct 2007 17:25:32 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 10 Oct 2007 17:25:32 +0000 (UTC) In-Reply-To: <1192032875.931672.131540@50g2000hsm.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: d55g2000hsg.googlegroups.com; posting-host=138.32.32.166; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.server:436181 X-Received-Date: Wed, 10 Oct 2007 13:25:32 EDT (text.usenetserver.com) On Oct 10, 11:14 am, xtremesp...@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@blorkflapple(1,1); end; 40 begin mytest.myproc@blorkflapple(1,2); end; NOPE begin mytest.myproc@blorkflapple(2,1); end; 80 PL/SQL procedure successfully completed. SQL> David Fitzjarrell