Friday, September 12, 2008

Overloading Stored Procedures - Oracle Stored Procedure Calling Another Procedure

I had a procedure that I wanted to reuse, and extend…my first thought was why not overload it. It took awhile to get to the syntax correct, but it turns out that there is no syntax…you just call the procedure. Keep in mind my procedures are in separate packages and this returns multiple resultsets (see VB.Net code below):

PROCEDURE GET_APP
(
IN_APP_ID IN NUMBER := 0,
OUT_CUR OUT MY_PACK.PROJECT_CUR,
OUT_CUR2 OUT MY_PACK.PROJECT_CUR
)
IS
BEGIN
MY_OTHER_PACK.GET_APP(IN_APP_ID, OUT_CUR);
OPEN OUT_CUR2 FOR
SELECT * FROM APPS where P_APP_ID = IN_APP_ID;
END GET_APP;


dr = comm.ExecuteReader()
newList = LoadFromDataReader(dr)
If dr.NextResult() Then
childList = LoadFromDataReader(dr)
newList(0).ChildrenApps = childList
End If

No comments: