Re: Please Help with PL/SQL Function Error PLS-00950

It has been too long for me to be sure but I suspect your use of an in-line view is consdiered to be a PL/SQL table.

You probably should consider upgrading to a version of Oracle that is supported in this century. If not for the performance, stability, and security ... for your resume'.

Also ... why return True and False as strings? Use the Boolean values.

Daniel Morgan

April wrote:

> 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 - 15:56:37 CST