Using a "DECLAREd" function inside a query inside PL/SQL
Date: 25 Mar 2004 09:58:43 -0800
Message-ID: <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
FUNCTION checkDate(
res NUMBER(3) := 0;
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 - 18:58:43 CET