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

privatebenjamin_at_hushmail.com (April) wrote in news:21e9f79.0203111124.419b0e7f_at_posting.google.com:

> 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.

I've looked and I can't see anything wrong, but I want to comment on your use of to_date(to_char(to_date(... You seem to be making these about three times more complicated than they need to be.

>
> 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-YYY
> Y');

Why not just TO_DATE(p_Month||p_Year,'MMYYYY')
>
>
> 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')
Same here. It took a while to figure out but this boils down to: SELECT NVL(a.Cont_reqd_isdate,TO_DATE(p_Month||p_Year,'MMYYYY)) But since you created tmpInterestDate := TO_DATE(p_Month||p_Year,'MMYYYY') why not simply NVL(a.Cont_reqd_Isdate,tmpInterestDate)

> 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') );
And here again it boils down to:
GROUP BY NVL(a.Cont_reqd_isdate,TO_DATE(p_Month||p_Year,'MMYYYY')) And since your selecting the MAX, why bother with the GROUP BY?

So why not reduce the whole thing to:
SELECT MAX(NVL(Cont_Reqd_Isdate,tmpInterestDate)

    INTO tmpISDate
  FROM tblProject
    WHERE ContPrefix = p_ContPrefix
    AND ContYr = p_ContYr
    AND ContSlaNum= p_ContSlaNum
    AND ConSlaVersion = p_ContSlaVersion     AND ContAwdNum = p_ContAwdnum;
>
> IF tmpISDATE <= tmpInterestDate THEN
> INSERVICE := 'True';
> ELSE
> INSERVICE := 'False';
> END IF;
>
>
> RETURN INSERVICE;
>
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK;
> RAISE;
>
> END SPCONTRACTINSERVICE;
But like I said, I see no reason it won't work as is.

-- 
Ken Denny
http://www.kendenny.com/

"The cat could very well be man's best friend but would never stoop to 
admitting it." - Doug Larson