Re: function returning boolean in a where clause

Ken -

You are partially correct. But consider this:

create or replace function foo(c1 in varchar2) return boolean is
begin
  if c1 = '1' then return true;
  else return false;
  end if;
end;
/

set verify off
prompt Enter a value on which to test the function: accept val

set serverout on
begin
  if foo('&val') = true then
    dbms_output.put_line('the return from the function is true');   else
    dbms_output.put_line('the return from the function is false');   end if;
end;
/

Conclusion: It is possible to do it, but only when the function is used under certain circumstances.

Bye,
TG

Kenneth, Koenraadt wrote:

> On Fri, 17 Jan 2003 22:56:18 +0530, "Sunil"
> <sunil_franklin_at_hotmail.com> wrote:
>
> >Is it possible to do it?
> >
> >Thanks,
> >Sunil.
> >
> >
>
> Hi Sunil,
>
> Nope.
>
> SQL> create or replace function foo(c1 in varchar2) return boolean
> 2 is
> 3 begin
> 4 return true;
> 5 end;
> 6 /
>
> Funktion er oprettet.
>
> SQL> select 1 from dual
> 2 where foo('1') = foo('1');
> where foo('1') = foo('1')
> *
> FEJL i linie 2:
> ORA-06552: PL/SQL: Statement ignored
> ORA-06553: PLS-382: expression is of wrong type
>
> BOOLEAN cannot be converted to any other built-in datatype, it is
> basicly an enumerated datatype with possible values
> (TRUE,FALSE,NULL).
>
> Instead, let 0 represent FALSE and all other values TRUE :
>
> SQL> create or replace function fii(c1 in varchar2) return
> binary_integer
> 2 is
> 3 begin
> 4 return 1;
> 5 end;
> 6 /
>
> Funktion er oprettet.
>
> SQL>
> SQL> select 1 from dual
> 2 where fii('1') = fii('1');
>
> 1
> ----------
> 1
>
> - Kenneth Koenraadt
Received on Fri Jan 17 2003 - 14:31:13 CST