Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Please Help with PL/SQL Function Error PLS-00950

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

From: April <privatebenjamin_at_hushmail.com>
Date: 13 Mar 2002 08:13:52 -0800
Message-ID: <21e9f79.0203130813.7ab61b50@posting.google.com>


The front-end application is in Access and it handles booleans differently than Oracle. However I could return -1,0 which I will do.

I didn't have any choice with the version of Oracle unfortunately.

damorgan <damorgan_at_exesolutions.com> wrote in message news:<3C8D2814.96AFB237_at_exesolutions.com>...
> 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 Wed Mar 13 2002 - 10:13:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US