Re: BOOLEAN parameter passed to a stored function

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 2 Jun 2001 19:25:56 -0500
Message-ID: <UsfS6.148$Z42.8200@nnrp1.sbc.net>

"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:4t9ihtcahls3dn9b1dnm1rprc9mojek7b0_at_4ax.com...
> Dear all,
>
> The following function takes BOOLEAN parameters:
>
> CREATE OR REPLACE FUNCTION sf_case
> (i_case1 IN BOOLEAN DEFAULT FALSE,
> i_case2 IN BOOLEAN DEFAULT FALSE)
> RETURN INTEGER
> IS
> v_result INTEGER;
> BEGIN
> IF i_case1 THEN
> v_result := 1;
> ELSIF i_case2 THEN
> v_result := 2;
> ELSE
> v_result := 0;
> END IF;
> RETURN v_result;
> END;
> /
>
> However, when I try it in SQL*Plus, logical expressions cannot be
> evaluated before passed into the stored function: (only literal TRUE
> and FALSE will be accepted)
>
> SQL> SELECT SF_CASE(2>1,2>1) FROM DUAL;
> SELECT SF_CASE(2>1,2>1) FROM DUAL
> *
> ERROR at line 1:
> ORA-00907: missing right parenthesis
>
> Anyone can help? (Oracle 8.0.5) Thanks in advance.
>
> Dino
>

you would need another function that would accept the '2>1' as a VARCHAR2 and return a BOOLEAN. e.g.

FUNCTION test_boolean
(as_string IN VARCHAR2
)RETURN BOOLEAN
IS
BEGIN
  IF as_string = '2>1' THEN
    RETURN FALSE;
  END IF;
  RETURN NULL;
END test_boolean;

and then use this function in your call:

SELECT SF_CASE(test_boolean('2>1'),test_boolean('2>1')) FROM DUAL;

i don't actually recommend that you do this, as i don't understand what it is
you are trying to achieve. the SF_CASE function looks strange, bizarre...

unfortunately, the CASE function was not introduced until after Oracle 8.0. a reasonable subset of the CASE type functionality can be achieved with the DECODE function...

for example, let's assume that a,b,c and d each represent some expression of type NUMBER. the pseudo-code " if a>b then c else d end-if " can be easily implemented in SQL using a combination of the DECODE and SIGN functions, e.g.

DECODE( SIGN( a - b ) , 1 , c , d )

the pseudo-code "if a>b then 1 elsif c>d then 2 else 0 end-if" can be expressed using builtin functions:

DECODE(SIGN(a-b),1,1,DECODE(SIGN(c-d),1,2,0))

implementing "and" and "or" logic in a DECODE is also doable, but it can start to get really ugly...

a better way to go, if you have complex logic to perform, is to code the logic as a PL/SQL function, and call the PL/SQL function from your SQL, e.g.

FUNCTION my_gt_test

(an_arg1 IN NUMBER
,an_arg2 IN NUMBER
,an_arg3 IN NUMBER
,an_arg4 IN NUMBER

)RETURN NUMBER
IS
BEGIN
  IF an_arg1 > an_arg2 THEN
    RETURN 1;
  ELSIF an_arg3 > an_arg4 THEN
    RETURN 2;
  END IF;
  RETURN 0;
END my_gt_test;

and use the function in your SQL:

SELECT my_gt_test(2,1,2,1 ) FROM DUAL;

HTH Received on Sat Jun 02 2001 - 19:25:56 CDT