Re: Stored Procedures

It might me an idea to post what error you get as well, because there's not really much information to go on here (as we don't know the error or the table structure you're using)... but a couple of things occur:

• You are not binding the results of your select to any variables - hence one error may be to do with that.
• Secondly, if the query returns more than one row you must use a cursor or table type bind variable.

But I don't know how to return multiple rows and columns to be honest, although it may be worth looking into the TABLE OF and %ROWTYPE operators and maybe you can declare a type which is a TABLE OF your_table%ROWTYPE and pass that out of your procedure.

But don't quote me!

Matt

David Small (102450.3705_at_CompuServe.COM) wrote:
: Can stored procedures return results from a query, much
: like a VIEW can? What I am looking for is something that can
: accept parameters (unlike a VIEW), but return results
: (columns and rows) that can be bound to variables. I have the
: following stored procedure...
:
: CREATE PROCEDURE qryGrowth (lSessionID NUMBER)
:
: BEGIN
:
: SELECT *
: FROM tblGrowth
: WHERE fldSessionID = lSessionID;
:
: END qryGrowth;
:
: When I try to create this procedure, I get a compile error. If I
: replace the SELECT statement with a DELETE, INSERT, or UPDATE
: statement, the procedure compiles fine. Is there a way to return
: a result set?
:
: Thanks,
: Dave
: 102450,3705_at_CompuServe.Com
Received on Tue Nov 19 1996 - 00:00:00 CET