Home » SQL & PL/SQL » SQL & PL/SQL » Problem when Function Return Type as Boolean
Problem when Function Return Type as Boolean [message #8989] Thu, 09 October 2003 06:05 Go to next message
Praveen Kumar B
Messages: 9
Registered: October 2003
Junior Member
Hai friends,

Here I am facing a problem when function return type is Boolean Datatype. Here I am giving the Function. Function is :-

Create Or Replace Function F2(Str Number) Return boolean Is
v_Str Number(13,2);
Num Number(13,2);
Begin
v_Str := Str;
Num := To_Number(v_Str);
Return true;
Exception
When Others Then
Return false;
End;
/

SQL> @f2
Input truncated to 1 characters

Function created.

And I am using this function named F2 as follows in SQL statement -

SQL> Select F2(1) from dual;
select f2(1) from dual
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Even I am using this function in Pl/Sql also it is giving same problem. "non-ORACLE exception".

Can anyone of you pls find the solutions and mail me.

Thanks & Regards,
Praveen Kumar B.
Re: Problem when Function Return Type as Boolean [message #8991 is a reply to message #8989] Thu, 09 October 2003 07:27 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Straight SQL does not recognize BOOLEANs; the BOOLEAN datatype is strictly a PL/SQL animal.

Your workaround is to make your function return a VARCHAR2, and have it pass back 'Y' or 'N'.

See how Oracle guru Tom Kyte addresses the subject.

HTH,

A.
Previous Topic: How to affect a result into a var from a dbms_sql execution ?
Next Topic: Problem when Function Return Type is Boolean
Goto Forum:
  


Current Time: Thu Mar 28 14:54:31 CDT 2024