Re: Calling procedures from within packages

Look up REF CURSOR.

What you are trying to do is very common and done in tens of thousands of apps.

Daniel Morgan

Sandy Murdock wrote:

> To Start--
>
> The reasons why I need to do this are long and drawn out. But...
>
> What I need to do is call a number of procedures from a package,
> sometimes I need to return values to the package which are immediately
> dumped into a cursor and returned as a record set to Active Server
> pages (we have a number of Packages that return cursor/recordsets fine
> no issue there).
>
> An example of what we have done in SQL server would look like this:
>
> If @FirstName is Not Null And len(rtrim(@FirstName)) > 0
> Begin
> exec spSelectPersonByName1A @FirstName, @Lastname, @Active
> End
> Else
> Begin
> Insert into tempTable ( LastName, Active )
> exec spSelectPersonByName1B @LastName, @Active
> End
>
> I want to be able to write something like this for Oracle (what's
> there is a small section). The Idea is to have a number of re-useable
> procedures that the package will call and use as needed to either
> insert into a table or ultimately return to the browser via a cursor.
>
> I have not been able to return a recordset from an Oracle procedure. I
> know that I cannot return one to a web-browser but I thought I should
> be able to return one to a package so that it can use it.
>
> Is this not possible? Or do I have to use Cursors with the Next
> command to loop (rather than work with the cursor like a table using
> pl/sql)?
>
> I can do this by writing a very large package, but then the smaller
> components inside cannot be re-used.
>
> I may not be explaining this well. Any tips would be appreciated.
>
> thank you.
Received on Wed Mar 13 2002 - 15:59:06 CST