Using a "DECLAREd" function inside a query inside PL/SQL

From: Bernard Drolet <bernard_at_drolet.com>
Date: 25 Mar 2004 09:58:43 -0800
Message-ID: <a9ccc9f9.0403250958.48fbdbc4@posting.google.com>

Hi, I would like to put in a function some code. This code will not change anything in the database.

as in

DECLARE
  res NUMBER(3) := 0;

  FUNCTION checkDate(

      validtime DATE,
      obsoletetime DATE

  ) RETURN INTEGER AS
  BEGIN
    IF validtime = obsoletetime THEN
      RETURN 1;
    END IF;
    RETURN 0;
  END; BEGIN
  DBMS_OUTPUT.ENABLE(1000000);   BEGIN
    SELECT 1
    INTO res
    FROM DUAL
    WHERE checkDate( SYSDATE, SYSDATE ) = 1;   EXCEPTION
    WHEN NO_DATA_FOUND THEN
      res := 2;
  END;   DBMS_OUTPUT.PUT_LINE(res);

END; If I run this, I get the following error PLS-00231: function 'CHECKDATE' may not be used in SQL

I know I could use some PRAGMA command and put the function in a package, but due to some business rules I am not allowed to create anything in the database, just to query it, e.g. I cannot create a package.

Is there any other way to use a function inside a query without putting it in a package ? As stated above, thefunction can be "trusted" and would only check variables, doing no changes.

Thank you

Bernard Drolet Received on Thu Mar 25 2004 - 11:58:43 CST