Re: executing procedure from sqlplus

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 21 Jan 1999 22:05:45 GMT
Message-ID: <36aba326.23588969@192.86.155.100>

A copy of this was sent to christian.arandaNOSPAM_at_NOSPAMiiginc.com (Christian M. Aranda)
(if that email address didn't require changing) On Thu, 21 Jan 1999 21:35:43 GMT, you wrote:

>I am attempting to execute a stored procedure from sqlplus using the
>following syntax:
>
>SQL> exec
>BBNSP_DERIV_SRVC_v220BR_PKG.BBNSP_DERIV_SRVC_v220BR('100','IS','200','','');
>begin
>BBNSP_DERIV_SRVC_v220BR_PKG.BBNSP_DERIV_SRVC_v220BR('100','IS','200','','');
>end;
>
> *
>ERROR at line 1:
>ORA-06550: line 1, column 7:
>PLS-00306: wrong number or types of arguments in call to
>'BBNSP_DERIV_SRVC_V220BR'
>ORA-06550: line 1, column 7:
>PL/SQL: Statement ignored
>
>here is the package specification:
>
>create or replace package bbnsp_deriv_srvc_v220br_pkg as
> type service_name_table_type is table of
>sw_case.bbnservicename%Type index by binary_integer;
>procedure bbnsp_deriv_srvc_v220br (
> siteid in sw_case.swsiteid%Type,
> servtype in sw_case.bbnservicetype%Type,
> custid in sw_customer.swcustomerid%Type,
> servicename out service_name_table_type,
> batch_size in int,
> out_batch_size in out int,
> status out int);
>end bbnsp_deriv_srvc_v220br_pkg;
>/
>show errors
>
>create or replace package body bbnsp_deriv_srvc_v220br_pkg as
>
>.
>.
>.
>
>I'm not too sure what I'm doing wrong. The datatypes match, I'm using
>5 input params...

but you have 3 OUTPUT params you are not dealing with ( and you have 7 params, not 5).

This is the way to call this from sqlplus:

declare

   l_servicename      bbnsp_deriv_srvc_v220br_pkg.service_name_table_type;
   l_out_batch_size   int;
   l_status           int;

begin

   bbnsp_deriv_srvc_v220br_pkg.bbnsp_deriv_srvc_v220br( '100' 'IS', '200',

                                                        l_service_name, 
                                                        '',
                                                        l_out_batch_size,
                                                        l_status );

end;
/

you have to deal with the OUT parameters...

>
>Any and all comments appreciated!
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 21 1999 - 16:05:45 CST