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 -> Please Help with PL/SQL Function Error PLS-00950

Please Help with PL/SQL Function Error PLS-00950

From: April <privatebenjamin_at_hushmail.com>
Date: 11 Mar 2002 11:24:23 -0800
Message-ID: <21e9f79.0203111124.419b0e7f@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.

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

Original text of this message

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