Calling procedures from within packages

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 - 13:41:44 CST