Re: Executing a procedure from SQL Plus

From: DeVerne Coleman <dsleuth_at_worldnet.att.net>
Date: Sun, 24 Sep 2000 03:49:22 GMT
Message-ID: <6Rez5.3854$6T.319471@bgtnsc05-news.ops.worldnet.att.net>

The way I test my procedures in sql plus is:

create variable c_people Personality.PeopleCur; exec Personality.GetNames(c_people, 'B%'); print c_people;

My procedures usually take a ref_cursor so I am not sure about printing the cursor from a package, but give it a try.

DeVerne Coleman

David Bradley <davidbradley_at_home.com> wrote in message news:qgLy5.194620$6y5.113005746_at_news2.rdc2.tx.home.com...
> Thank you for your solution. Your example did execute the procedure. How
> can I also display the contents of c_people to the screen?
>
> Sorry, I am new at some of this stuff.
>
>
>
> Thanks,
>
> David
>
>
>
>
> Mark <cdo_at_lambic.co.uk> wrote in message
> news:39CB791B.F0F1D079_at_lambic.co.uk...
> > 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;
> > > > > >
> > > > > > END Personality;
>
>
Received on Sat Sep 23 2000 - 22:49:22 CDT