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

bernard_at_drolet.com (Bernard Drolet) wrote in message news:<a9ccc9f9.0403250958.48fbdbc4_at_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

In 8i and later you shouldn't need the pragma directive. As you don't state your version, who can tell what is going on?

Sybrand Bakker
Senior Oracle DBA Received on Fri Mar 26 2004 - 12:18:42 CET