Passing Cursor to Stored Procedure

[doctorfly]

Hi,

Does anyone know how to create a procedure that calls another procedure with a cursor and then uses that cursor in a loop?

procedure SP_ASSIGN_HOUSING
AS
myHousingRankingCursor HOUSINGTYPES.HOUSING_CURSOR;
BEGIN

/* call SP with cursor */
SP_GET_HOUSING_LIST(myHousingRankingCursor);

/* use cursor in loop
-- the next line causes an error that my cursor isn't
-- defined, even though it's defined above.
*/
FOR ranking_rec IN myHousingRankingCursor
LOOP
IF ranking_rec.Ranking=4 then
INSERT INTO
topoflist
VALUES(sq_topoflistuid.nextval, ranking_rec.employeeuid, 3);
EXIT LOOP;
END IF;
END LOOP;
END;

 

[SantaMufasa]

Doc",

We define cursors with "SELECT..." statements. Perhaps I am unschooled in recent changes to arguments for stored procedures, but I have never seen a CURSOR act as an argument to a stored procedure. So, among the issues I can recommend for success are: 1) For simplicity, change your LOOP definition to read

...FOR ranking_rec IN (SELECT <expressions> FROM <table> WEHRE <condition> ORDER BY <expression_list>) LOOP...END LOOP;

2) Do not try to pass a CURSOR as an argument for a stored procedure; pass expressions, if necessary.

Let us know what you discover,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:54 (27Jun04) UTC (aka "GMT" and "Zulu"), 12:54 (27Jun04) Mountain Time)

 

[doctorfly]

Thanks for the reply Mufasa. Unfortunately, I have to pass a cursor to the stored procedure because I am also using the stored procedure to return results to an ASP.NET program. Using the cursor for returning results to ASP.NET works great. I just can't seem to make use of it within another stored procedure.

Any other ideas?

Thanks!

 

[vivekm]

Please provide declaration of cursor as well as the parameters used in the called procedure.

Thanks,
Vivek