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

When I saw how simple your changes were, I wondered myself why mine were so convoluted. But I realized that the reason I did all that to_char(to_date(to_char etc. was

1. due to the date formatting on the server. I was receiving error messages unless I converted to dd-MON-yyyy and
2. in order to properly compare the cont_reqd_isdate(dd-mm-yyyy) and the tmpInterestDate (mm-yyyy). The mm-yyyy defaulted to a dd of 01, so the cont_reqd_isDate would never be equal to the tmpInterestDate when they were in the same month, which was one of my objectives.

But I agree, still, the function has problems...

Thanks for your help

Ken Denny <ken_at_kendenny.com> wrote in message news:<Xns91CE986E98250kendenny_at_65.82.44.7>...
> 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.
Received on Wed Mar 13 2002 - 10:21:42 CST