Home » SQL & PL/SQL » SQL & PL/SQL » how to execute this function with boolean variable as input
how to execute this function with boolean variable as input [message #18762] Wed, 13 February 2002 08:09 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
I have a function with a boolean variable as the "in" parameter.
how do i execute through sqlplus and what should be the parameter list.

create or replace FUNCTION fun_test1(
Input in varchar,
case1 in boolean,
output1 out varchar)
return boolean IS
BEGIN
output1:= 'match';
if output1 ='not match' then
return false;
else
return true;
end if;
EXCEPTION WHEN OTHERS THEN
return false;
end fun_test1;

When i tried to give it fails saying wrong number of arguments.How to execute this then.. with true and false values and also how to print the boolean value

declare
bol1 boolean;
begin
bol1:= fun_test1('one',true);
dbms_output.put_line(bol2);
end;
Re: how to execute this function with boolean variable as input [message #18765 is a reply to message #18762] Wed, 13 February 2002 11:44 Go to previous messageGo to next message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi

In function u cannot have a output parameter.
Function can return only one value.

I don't know what you are trying to do in your example..
but move the out variable in to declarations section like..

create or replace FUNCTION fun_test1(
Input varchar,
case1 boolean)
return boolean IS
--declaration section
output1 boolean;

BEGIN
output1:= 'match';
if output1 ='not match' then
return false;
else
return true;
end if;
EXCEPTION WHEN OTHERS THEN
return false;
end fun_test1;

and you can execute the function in a select query also..

select fun_test1('one', true) from dual;
Re: how to execute this function with boolean variable as input [message #18771 is a reply to message #18765] Wed, 13 February 2002 22:34 Go to previous messageGo to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
it does not work..
CREATE OR REPLACE FUNCTION WNS.fun_test1(Input in varchar,
case1 in boolean
) return boolean IS
output1 varchar2(10);
BEGIN
output1:= 'match';

if output1 ='not match' then
return false;
else
return true;
end if;
EXCEPTION WHEN OTHERS THEN
return false;
end fun_test1;
/

SQL> select fun_test1('one',true) from dual;
select fun_test1('one',true) from dual
*
ERROR at line 1:
ORA-00904: invalid column name

what parameters should be given...
Re: how to execute this function with boolean variable as input [message #18776 is a reply to message #18762] Wed, 13 February 2002 23:51 Go to previous messageGo to next message
ratna
Messages: 7
Registered: October 2001
Junior Member
restrictions when calling functions from sql expressions:
a user defined fuction can take only in parameters not out or in out.
data types must be valid sql types,char,date or number.
data types can not be pl/sql types such as boolean,record ot table.
Re: how to execute this function with boolean variable as input [message #18783 is a reply to message #18762] Thu, 14 February 2002 00:25 Go to previous messageGo to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
Ok...thanks for the info..
if i want to execute it through anonymous block,
how do i execute it.
I have tried
declare
bol1 boolean;
begin
bol1:= fun_test1('one',true);
end;

It still gives me the error.

What parameters has to be given to execute this..

any help in this..
Re: how to execute this function with boolean variable as input [message #18792 is a reply to message #18765] Thu, 14 February 2002 06:30 Go to previous message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi,
Ratna is correct you can not return non-sql data types from functions when you use them in your sql statements...

try this
BIN@BINTEST> create or replace FUNCTION fun_test
2 Input varchar,
3 case1 boolean)
4 return boolean IS
5 --declaration section
6 output1 varchar2(20);
7 BEGIN
8 output1:= 'match';
9 if output1 ='not match' then
10 return false;
11 else
12 return true;
13 end if;
14 EXCEPTION WHEN OTHERS THEN
15 return false;
16 end fun_test1;
17 /

Function created.

BIN@BINTEST> declare
2 l_input1 varchar2(10);
3 l_case1 boolean;
4 l_output1 boolean;
5 begin
6 l_input1 := 'one';
7 l_case1 := TRUE;
8 if ( fun_test1(l_input1, l_case1)) then
9 dbms_output.put_line('YES');
10 else
11 dbms_output.put_line('NO');
12 end if;
13* end;
/

YES

PL/SQL procedure successfully completed.

------------------------------------------

see.. here the point is
you can have any datatype supported by plsql
in your functions and procedure(btw you can have more than one input parameter in a function,
but a function can return only one value...this includes arrays and objects)
but when you use them in your sql statement,
you can not return a non-sql data type....

Bala
Previous Topic: dbms_stats
Next Topic: Re: B*-tree indexes
Goto Forum:
  


Current Time: Thu Sep 23 12:00:32 CDT 2021