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

"Bernard Drolet" <bernard_at_drolet.com> 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

to use the function in sql it must exist where the parser can find it -- in the database, as a stand-alone or packaged function

depending on your version of oracle, you do not need to specify any pragmas for the function to be used in SQL

i'm assuming your example is just an example, otherwise there would be two deficiencies:
1) this use of a function in the where clause would disallow the optimizer from choosing any available indexes on the column(s) being passed to the function -- you would have to create a function based index, assuming the function is deterministic
2) hopefully the function code is just a sample, since the date equality comparison can be done with a simple thisdate = thatdate in the where clause

;-{ mcs Received on Fri Mar 26 2004 - 12:07:37 CET