PL/SQL Tables connot be used in this Version

From: April <privatebenjamin_at_hushmail.com>
Date: 6 Mar 2002 13:13:40 -0800
Message-ID: <21e9f79.0203061313.51c4299c@posting.google.com>

Hello,

I receive the error below when compiling the function below, although the
function still registers as valid:

"PLS-00950: In this version, PL/SQL tables can not be used in this SQL statement."

The Oracle Version is 7.3.4.

It stops at the line SELECT MAX(ISDATE)... I have tested the sql statement for the cursor on its own and it works, as well
I have created this same function except for the MAX statement with the subquery, and that also works.

Adding in the MAX and selecting from the subquery into a cursor seems to be the problem?

How can I solve this problem?

Below is the function.
Thanks for your help,
April

CREATE OR REPLACE FUNCTION SPCONTRACTINSERVICE(

p_ContPrefix 	  IN VARCHAR2,
p_ContYr	  IN VARCHAR2,
p_ContSlaNum 	  IN VARCHAR2,
p_ContSlaVersion  IN VARCHAR2,
p_ContAwdNum	  IN VARCHAR2,
p_Month 	  IN VARCHAR2,
p_Year 		  IN VARCHAR2)

RETURN VARCHAR2 IS INSERVICE VARCHAR2(5); tmpISDate tblProject.Cont_reqd_ISDate%Type; tmpInterestDate Date:=TO_DATE(TO_CHAR(TO_DATE(p_Month,'MM'),'MON')||'-'||p_Year,'MON-YYYY');

CURSOR c_IsDate IS

	SELECT MAX(ISDATE) 
	FROM(	   
	    SELECT TO_DATE(TO_CHAR(NVL(a.CONT_REQD_ISDATE,
		TO_DATE(TO_CHAR(TO_DATE(p_Month,'MM'),'MON')||'-'||p_Year,'MON-YYYY')),'MON')


		||'-'||TO_CHAR(NVL(a.Cont_Reqd_ISDate,

		TO_DATE(TO_CHAR(TO_DATE(p_Month,'MM'),'MON')||'-'||p_Year,'MON-YYYY')),'YYYY'),'MON-YYYY')
ISDate
		FROM  tblProject a
		WHERE a.ContPrefix = p_ContPrefix
		AND	  a.ContYr = p_ContYr
		AND	  a.ContSlaNum = p_ContSlaNum
		AND	  a.ContSlaVersion = p_ContSlaVersion
		AND   a.ContAwdNum = p_ContAwdnum
		GROUP BY TO_DATE(TO_CHAR(NVL(a.CONT_REQD_ISDATE,
		TO_DATE(TO_CHAR(TO_DATE(p_Month,'MM'),'MON')||'-'||p_Year,'MON-YYYY')),'MON')


		||'-'||TO_CHAR(NVL(a.Cont_Reqd_ISDate,

		TO_DATE(TO_CHAR(TO_DATE(p_Month,'MM'),'MON')||'-'||p_Year,'MON-YYYY')),'YYYY'),'MON-YYYY')
		);
			  
BEGIN
   	/*check to see if the contract in service date is greater than the
interest month, year parameters.
	if greater than or equal to month/year parameter, the contract IS In

service*/         

	OPEN c_ISDate;
	FETCH c_ISDATE INTO tmpISDate;
	CLOSE c_ISDATE;
		  
	SELECT 
	   DECODE(GREATEST(tmpISDate,tmpInterestDate),tmpISDate,
		   DECODE(tmpISDate,tmpInterestDate,'True','False'),'True') 
	INTO INSERVICE
	FROM  tblProject a
	WHERE a.ContPrefix = p_ContPrefix
	AND	  a.ContYr = p_ContYr
	AND	  a.ContSlaNum = p_ContSlaNum
	AND	  a.ContSlaVersion = p_ContSlaVersion
	AND   a.ContAwdNum = p_ContAwdnum;
	
	RETURN INSERVICE;
   

EXCEPTION
    WHEN OTHERS THEN

       ROLLBACK;
	   RAISE;
  	    

END SPCONTRACTINSERVICE;
/ Received on Wed Mar 06 2002 - 15:13:40 CST