How to use BOOLEAN type in SELECT statement

Question

I have a PL SQL function with BOOLEAN in parameter   function get something name in varchar2  ignore notfound in boolean     This function is a part of 3rd party tool  I cannot change this   I would like to use this function inside a SELECT statement like this    select get something  NAME   TRUE  from dual    This does not work  I get this exception      ORA-00904   TRUE   invalid identifier   As I understand it  keyword TRUE is not recognized   How can I make this work

User · Answer

select get something  NAME   sys diutil int to bool 1   from dual

User · Answer

You can build a wrapper function like this   function get something name in varchar2                     ignore notfound in varchar2  return varchar2 is begin     return get something  name   upper ignore notfound     TRUE      end    then call   select get something  NAME    TRUE   from dual    It s up to you what the valid values of ignore notfound are in your version  I have assumed  TRUE  means TRUE and anything else means FALSE

User · Answer

Compile this in your database and start using boolean statements in your querys   note  the function get s a varchar2 param  so be sure to wrap any  strings  in your statement   It will return 1 for true and 0 for false   select bool    abc   lt   bfg     from dual   CREATE OR REPLACE function bool p str in varchar2  return varchar2   is  begin   execute immediate   begin if    P str    then            v res     1         else            v res     0         end if  end   using out v res          return v res    exception    when others then      return      p str     is not a boolean expr     end

User · Answer

How about using an expression which evaluates to TRUE  or FALSE     select get something  NAME   1   1  from dual

User · Answer

From documentation      You cannot insert the values TRUE and FALSE into a database column  You cannot select or fetch column values into a BOOLEAN variable  Functions called from a SQL query cannot take any BOOLEAN parameters  Neither can built-in SQL functions such as TO CHAR  to represent BOOLEAN values in output  you must use IF-THEN or CASE constructs to translate BOOLEANvalues into some other type  such as 0 or 1   Y  or  N    true  or  false   and so on    You will need to make a wrapper function that takes an SQL datatype and use it instead

User · Answer

The answer to this question simply put is  Don t use BOOLEAN with Oracle-- PL SQL is dumb and it doesn t work   Use another data type to run your process     A note to SSRS report developers with Oracle datasource   You can use BOOLEAN parameters  but be careful how you implement   Oracle PL SQL does not play nicely with BOOLEAN  but you can use the BOOLEAN value in the Tablix Filter if the data resides in your dataset   This really tripped me up  because I have used BOOLEAN parameter with Oracle data source   But in that instance I was filtering against Tablix data  not SQL query     If the data is NOT in your SSRS Dataset Fields  you can rewrite the SQL something like this using an INTEGER parameter        lt ReportParameter Name  paramPickupOrders  gt     lt DataType gt Integer lt  DataType gt     lt DefaultValue gt       lt Values gt         lt Value gt 0 lt  Value gt       lt  Values gt     lt  DefaultValue gt     lt Prompt gt Pickup orders  lt  Prompt gt     lt ValidValues gt       lt ParameterValues gt         lt ParameterValue gt           lt Value gt 0 lt  Value gt           lt Label gt NO lt  Label gt         lt  ParameterValue gt         lt ParameterValue gt           lt Value gt 1 lt  Value gt           lt Label gt YES lt  Label gt         lt  ParameterValue gt       lt  ParameterValues gt     lt  ValidValues gt   lt  ReportParameter gt         lt Query gt   lt DataSourceName gt Gmenu lt  DataSourceName gt   lt QueryParameters gt     lt QueryParameter Name   paramPickupOrders  gt       lt Value gt  Parameters paramPickupOrders Value lt  Value gt     lt  QueryParameter gt   lt CommandText gt      where            paramPickupOrders   0 AND ordh PICKUP FLAG    N          OR  paramPickupOrders   1 AND ordh PICKUP FLAG    Y      If the data is in your SSRS Dataset Fields  you can use a tablix filter with a BOOLEAN parameter        lt  ReportParameter gt   lt ReportParameter Name  paramFilterOrdersWithNoLoad  gt     lt DataType gt Boolean lt  DataType gt     lt DefaultValue gt       lt Values gt         lt Value gt false lt  Value gt       lt  Values gt     lt  DefaultValue gt     lt Prompt gt Only orders with no load  lt  Prompt gt   lt  ReportParameter gt         lt Tablix Name  tablix dsMyData  gt   lt Filters gt     lt Filter gt       lt FilterExpression gt            Parameters paramFilterOrdersWithNoLoad Value false           or  Parameters paramFilterOrdersWithNoLoad Value true and Fields LOADNUMBER Value 0       lt  FilterExpression gt       lt Operator gt Equal lt  Operator gt       lt FilterValues gt         lt FilterValue DataType  Boolean  gt  true lt  FilterValue gt       lt  FilterValues gt     lt  Filter gt   lt  Filters gt

User · Answer

PL SQL is complaining that TRUE is not a valid identifier  or variable   Set up a local variable  set it to TRUE  and pass it into the get something function

User · Answer

The BOOLEAN data type is a PL SQL data   type  Oracle does not provide an   equivalent SQL data type       you can   create a wrapper function which maps a   SQL type to the BOOLEAN type    Check this  http   forums datadirect com ddforums thread jspa threadID 1771 amp tstart 0 amp messageID 5284

User · Answer

You can definitely get Boolean value from a SELECT query  you just can t use a Boolean data-type   You can represent a Boolean with 1 0   CASE WHEN  10  gt  0  THEN 1  ELSE 0 END  It can be used in SELECT QUERY   SELECT CASE WHEN  10  gt  0  THEN 1  ELSE 0 END AS MY BOOLEAN COLUMN   FROM DUAL   Returns  1  in Hibernate Mybatis etc 1 is true   Otherwise  you can get printable Boolean values from a SELECT   SELECT CASE WHEN  10  gt  0  THEN  true  ELSE  false  END AS MY BOOLEAN COLUMN  FROM DUAL   This returns the string  true

User · Answer

With Oracle 12  you can use the WITH clause to declare your auxiliary functions  I m assuming your get something function returns varchar2   with   function get something  name varchar2  ignore notfound number    return varchar2    is   begin     -- Actual function call here     return get something name  not ignore notfound   0     end get something      -- Call auxiliary function instead of actual function select get something   NAME   1  from dual    Of course  you could have also stored your auxiliary function somewhere in the schema as shown in this answer  but by using WITH  you don t have any external dependencies just to run this query  I ve blogged about this technique more in detail here

[sql] How to use BOOLEAN type in SELECT statement

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle10g

Examples related to ora-00904