Compiling Recursive Functions

[mwolf00]

Is there any way to compile a recursive function without it "failing" the first time? It does seem to actually complile, but shows an error in the output. If you compile it a second time, it works without error messages.

CREATE OR REPLACE FUNCTION Rbm_Fgetholdoverlength(
	   txtlease	  		   VARCHAR2,
	   dteff 	  		   DATE)
RETURN NUMBER IS
	   intMonths		   PLS_INTEGER := 0;
	   intResult		   PLS_INTEGER;
BEGIN
	 SELECT COUNT(*) INTO intResult
	   FROM RBM_MANAGEMENT m, EFOLDER f, RBM_TACTION a
	  WHERE m.efolderid = f.efolderid
	    AND m.txtactiontype = a.action_code
		AND f.estagename = 'Request Completed'
		AND a.name LIKE '%Holdover%'
		AND m.txtLeaseNumber = txtlease
		AND TO_CHAR(m.dtrequesteffective, 'MM') = TO_CHAR(dteff, 'MM')
		AND TO_CHAR(m.dtrequesteffective, 'YYYY') = TO_CHAR(dteff, 'YYYY');

	 IF intResult < 1 THEN
	 	RETURN 0;
	 END IF;

	 SELECT rbm_fgetholdoverlength(txtlease, ADD_MONTHS(dteff, -1)) INTO intResult FROM DUAL;

	 RETURN 1 + intResult;
END Rbm_Fgetholdoverlength;

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Dagon]

It will probably fail because you are creating an infinite loop and Oracle will detect this. You need to have some way of determining at what point the recursive function stops calling itself.