Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calling a BOOLEAN returning function from SQL

882789Aug 1 2012 — edited Aug 2 2012
Hello All,

I have created below function which return BOOLEAN value :
CREATE OR REPLACE FUNCTION FUNC_1
(
P_EMPID IN emp.empno%type
)RETURN BOOLEAN
AS
L_VAR NUMBER;
BEGIN
SELECT 1 INTO L_VAR
FROM EMP
WHERE EMPNO = P_EMPID;

IF L_VAR = 1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
Now I want to call this function from SELECT but I know that SQL does not support BOOLEAN value so I tried in other way i.e. via CASE
SELECT CASE FUNC_1(7788)
       WHEN TRUE THEN 'TRUE'
       WHEN FALSE THEN 'FALSE'
       ELSE 'NULL'
       END CASE
FROM DUAL
But it is giving me error "ORA-00904: "FALSE": invalid identifier'

How can I achieve this ?

Thanks & Regards,
Rakesh
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 30 2012
Added on Aug 1 2012
5 comments
47,699 views