Execute Immediate, executing procedure at runtime passing Cursor

From: Bob <bobby_at_caseymusic.net>
Date: 30 May 2003 17:50:08 -0700
Message-ID: <87d90b1f.0305301650.3cd9aba5@posting.google.com>

I'm trying to pass a cursor to a Dynamically Executed Procedure. The code works, except when I added the cursor "rAPP" to the parameter list in my EXECUTE IMMEDIATE line of code. Does anyone know how to pass a cursor to a Procedure using Execute Immediate? Thanks a million in advance? Here's my code so far:

PROCEDURE VALIDATE_PROGRAM

       (pNumApp_ID     IN CCZ.CCZ460_APPLICATION.APP_ID%TYPE,
	pNumPGM_ID     IN CCZ.CCZ500_PROGRAM.PGM_ID%TYPE,
        pStrUSER_ID    IN CCZ.CCZ100_USER.USER_ID%TYPE,
	pStrReturn     OUT VARCHAR2) IS

/*

*/
plsql_block VARCHAR2(500);
lstrReturn VARCHAR2(500);
rAPP CCZ.CCZ460_APPLICATION%ROWTYPE;
BEGIN    --Get number of occupants and income of household    OPEN cAPPLICATION(pNumAPP_ID);
   FETCH cAPPLICATION INTO rAPP;
   CLOSE cAPPLICATION;    

	--LOOP THRU GETTING ALL SP_NAMES IN CCZ210, CCZ200 
	FOR rPGM_VALID IN cVALIDATIONS(0,pNumPGM_ID) LOOP

	   plsql_block := 'BEGIN ' || rPGM_VALID.SP_NAME || '(:rAPP

:pNumAPP_ID,:pNumPGM_ID,:pStrUSER_ID, :pStrReturn); END;';

           EXECUTE IMMEDIATE plsql_block USING IN rAPP, IN pNumApp_ID,IN pNumPGM_ID,IN pStrUSER_ID,OUT lstrReturn;

   END LOOP;    pStrReturn := 'OK';

END VALIDATE_PROGRAM; Received on Fri May 30 2003 - 19:50:08 CDT