Re: Executing a procedure from SQL Plus

David Bradley wrote:
>
> I have tried all kinds of ways to execute this. My procedure should return
> all rows from a table where the LAST_NAME is like the passed parameter. The
> procedure works fine when called from my VB app. Could you give me the
> syntax to execute this in SQL Plus window with the declaration of the
> variables?

DECLARE
  c_people Personality.PeopleCur;
BEGIN
  Personality.GetNames(c_people, 'B%');
END;
/

This will execute your procedure, but you won't be able to do anything with the result. Processing of the result cursor should be handled inside the anonymous block (after the procedure call and before END;).  

> Carl Hathaway <chathaw2_at_ford.com> wrote
> > Additionally as the first parameter is an IN OUT then you need to declare
> > a variable to hold the returning value.
> >
> > spencer <spencerp_at_swbell.net> wrote
> > > the GetNames procedure is defined with two arguments, and you are
> > > only supplying one argument in the call. that's what oracle is telling
> > > you, that you have supplied only one argument.
> > >
> > > "David Bradley" <davidbradley_at_home.com> wrote
> > > > I created the package and package body below. When I call this from
> > > > my Visual Basic app using ADO it runs fine. When I try to execute it
> > > > from SQLPlus I get the following error. Am I doing something wrong.
> >

 Error ----------------------------------------------------------------------


> > > >

> > > > SQLWKS> execute Personality.GetNames('B%');

> > > > Personality.GetNames('B%');

> > > > *

> > > > ORA-06550: line 2, column 2:

> > > > PLS-00306: wrong number or types of arguments in call to 'GETNAMES'

> > > > ORA-06550: line 2, column 2:

> > > > PL/SQL: Statement ignored

> > > >

> > > >

> > > >

> > > > -- Creation of package and package body 

> > > >

> > > > CREATE OR REPLACE PACKAGE Personality

> > > > IS

> > > >    TYPE PeopleCur IS REF CURSOR RETURN sti_personnel%ROWTYPE;

> > > >    PROCEDURE  GetNames(PeopleCursor in out PeopleCur, plastname in

> > > > sti_personnel.LAST_NAME%TYPE);

> > > > END Personality;

> > > >

> > > > /

> > > >

> > > >

> > > > CREATE OR REPLACE PACKAGE BODY Personality

> > > > AS

> > > >

> > > > PROCEDURE GetNames(PeopleCursor in out Peoplecur, plastname in

> > > > sti_personnel.LAST_NAME%TYPE)

> > > >    IS

> > > >    BEGIN

> > > >

> > > >    OPEN PeopleCursor FOR

> > > >      SELECT * FROM sti_personnel WHERE LAST_NAME like  plastname ;

> > > >

> > > > END GetNames;