Re: Overloading Stored Proc

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 16 Apr 2002 06:38:45 -0700
Message-ID: <a9h9h50k1c@drn.newsguy.com>

In article <9390905d.0204151648.1ea901d4_at_posting.google.com>, trhaverstock_at_yahoo.com says...
>
>I have an overloaded stored proc in a packagein Oracle 8.1.6. It
>looks something like this:
>
>MyProc (
> v1 VARHCAR2 IN
> v2 VARHCAR2 IN
> a1 VARCHAR2 OUT)
>
>MyProc (
> v1 VARHCAR2 IN
> v2 VARHCAR2 IN
> v3 VARHCAR2 IN
> a1 VARCHAR2 OUT
> a2 VARCHAR2 OUT
> a3 VARCHAR2 OUT)
>
>It compiles fine. And I have a test M$ Active Server Page that can
>call both versions of this proc without a problem and get the correct
>results.
>
>A collegue that is attempting to call this proc from Powerbuilder
>can't seem to figure out how to call it. In fact, he told me he
>couldn't figure out how to call it from SQL Plus. So I tried to call
>it from SQL Plus and had wierd errors that led me to believe it was
>confused with one OUT in the one proc and three OUTs in the other.
>
>Can someone enlighten me on the correct syntax on how to declare the
>vars and call this from SQL Plus? If I make both have the same # of
>OUT vars it seems to like that better. I gotta believe it's possible
>to do what I'm attempting since it's working from an ASP and that I'm
>just clueless on SQLPlus.
>
>Any help appreciated!
>
>Todd
>trhaverstock_at_yahoo.com

You can do it one of two ways as demonstrated below:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package my_pkg   2 as
  3
  4 procedure MyProc (
  5 v1 in varchar2,
  6 v2 in varchar2,
  7 a1 out varchar2);
  8
  9 procedure MyProc (

 10   v1 in varchar2,
 11   v2 in varchar2,
 12   v3 in varchar2,

 13 a1 out varchar2,
 14 a2 out varchar2,
 15 a3 out varchar2);
 16
 17 end;
 18 /

Package created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show errors

No errors.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package body my_pkg   2 as
  3
  4 procedure MyProc (
  5 v1 in varchar2,
  6 v2 in varchar2,
  7 a1 out varchar2)
  8 as begin null; end;
  9
 10 procedure MyProc (

 11   v1 in varchar2,
 12   v2 in varchar2,
 13   v3 in varchar2,

 14 a1 out varchar2,
 15 a2 out varchar2,
 16 a3 out varchar2)
 17 as begin null; end;
 18
 19 end;
 20 /

Package body created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x1 varchar2(20);
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x2 varchar2(20);
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x3 varchar2(20);
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec my_pkg.myProc( v1 => 'x', v2 => 'y', a1

=> :x1 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec my_pkg.myProc( 'x', 'y', :x1 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec my_pkg.myProc( v1 => 'x', v2 => 'y', v3 => 'z', a1 => :x1, a2 => :x2, a3 => :x3 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec my_pkg.myProc( 'x', 'y', 'z', :x1, :x2, :x3 );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Apr 16 2002 - 08:38:45 CDT