Home » SQL & PL/SQL » SQL & PL/SQL » Boolean variable in PL/SQL (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Boolean variable in PL/SQL [message #555815] Tue, 29 May 2012 02:42 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi All ,

I want to display Boolean value of a variable in DBMS Print statement. I am able to do it by using simple if condition checking .
How to print the value of Boolean variable directly ?

SQL> declare
  2    boo boolean := true;
  3  begin
  4  
  5   if boo then
  6      dbms_output.put_line('boolean variable value TRUE  by checking if condition ');
  7    end if;
  8    dbms_output.put_line(' Directly print the value Boolean variable value '|| boo);
  9  end;
 10  
 11  /
 
declare
  boo boolean := true;
begin

 if boo then
    dbms_output.put_line('boolean variable value TRUE  by checking if condition ');
  end if;
  dbms_output.put_line(' Directly print the value Boolean variable value '|| boo);
end;
 
ORA-06550: line 9, column 24:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored



Thanks
SaiPradyumn
Re: Boolean variable in PL/SQL [message #555820 is a reply to message #555815] Tue, 29 May 2012 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
dbms_output can only display variables of type string or that have an implicit conversion function to string.
This is not the case of BOOLEAN data type.

Regards
Michel
Re: Boolean variable in PL/SQL [message #555830 is a reply to message #555820] Tue, 29 May 2012 03:33 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Hi Michel ,

Are there any functions that can apply on Boolean data type,so that we can get the actual values of that variable

Thanks
SaiPradyumn
Re: Boolean variable in PL/SQL [message #555832 is a reply to message #555830] Tue, 29 May 2012 04:08 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
It depends on your definition of "the actual values of that variable".
I would use simple CASE expression, something like e.g.
  case boo
    when true then <the string "value" for TRUE>
    when false then <the string "value" for FALSE>
    else <the string "value" for NULL>
  end

You may create user-defined function returning that expression.
Re: Boolean variable in PL/SQL [message #555883 is a reply to message #555832] Tue, 29 May 2012 11:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
In addition to the CASE method, there are a couple of undocumented functions, as demonstrated below.

SYS@orcl_11gR2> grant execute on sys.dbms_sqltcb_internal to scott
  2  /

Grant succeeded.

SYS@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> declare
  2    boo boolean;
  3  begin
  4    boo := true;
  5    dbms_output.put_line (case boo when true then 'true' when false then 'false' end);
  6    dbms_output.put_line (sys.dbms_sqltcb_internal.i_convert_from_boolean (boo));
  7    dbms_output.put_line (sys.diutil.bool_to_int (boo));
  8    boo := false;
  9    dbms_output.put_line (case boo when true then 'true' when false then 'false' end);
 10    dbms_output.put_line (sys.dbms_sqltcb_internal.i_convert_from_boolean (boo));
 11    dbms_output.put_line (sys.diutil.bool_to_int (boo));
 12  end;
 13  /
true
TRUE
1
false
FALSE
0

PL/SQL procedure successfully completed.

Re: Boolean variable in PL/SQL [message #555925 is a reply to message #555883] Wed, 30 May 2012 00:07 Go to previous messageGo to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
great find Un-Documented functions.

thanks very much Barbara Smile
Re: Boolean variable in PL/SQL [message #555934 is a reply to message #555925] Wed, 30 May 2012 01:13 Go to previous message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you can use (with care) diutil package you should not (must not) use dbms_sqltcb_internal; if even a DBA can't use it (by default) there is a reason.

Regards
Michel
Previous Topic: mutating table error
Next Topic: Dynamic type
Goto Forum:
  


Current Time: Tue Oct 28 23:38:17 CDT 2025