| how to execute this function with boolean variable as input [message #18762] |
Wed, 13 February 2002 08:09  |
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  |
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 |
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 #18792 is a reply to message #18765] |
Thu, 14 February 2002 06:30 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
