Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Please Help with PL/SQL Function Error PLS-00950
When compiled this function below returns the error
"PLS-00950: PL/SQL tables cannot be used in this version"
The version is Oracle 7.3.4 and I am not using PL/SQL tables, as far
as I know!
The error occurs at the "SELECT MAX(ISDATE) INTO tmpISDate.." line. The sql statement does work by itself when not contained in a pl/sql function.
The purpose of the function is to gather the dates from records based on the parameters passed in, and return the Latest date.
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'); BEGIN /*check to see if the contract in service date is less than the interest month, year parameters. if less than or equal to month/year parameter, the contract IS In service*/ SELECT MAX(ISDATE) INTO tmpISDate 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') ); IF tmpISDATE <= tmpInterestDate THEN INSERVICE := 'True'; ELSE INSERVICE := 'False'; END IF; RETURN INSERVICE;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; RAISE;
END SPCONTRACTINSERVICE; Received on Mon Mar 11 2002 - 13:24:23 CST