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?
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.
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 > 0) THEN 1 ELSE 0 END (It can be used in SELECT QUERY)
SELECT CASE WHEN (10 > 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 > 0) THEN 'true' ELSE 'false' END AS MY_BOOLEAN_COLUMN
FROM DUAL
This returns the string 'true'.
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.
From documentation:
You cannot insert the values
TRUEandFALSEinto a database column. You cannot select or fetch column values into aBOOLEANvariable. Functions called from aSQLquery cannot take anyBOOLEANparameters. Neither can built-inSQLfunctions such asTO_CHAR; to representBOOLEANvalues in output, you must useIF-THENorCASEconstructs to translateBOOLEANvalues into some other type, such as0or1,'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.
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&tstart=0&messageID=5284
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.
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''<''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;
/
select get_something('NAME', sys.diutil.int_to_bool(1)) from dual;
Oracle Database 23c has added the Boolean data type to SQL. So you can now call PL/SQL functions returning Boolean in queries as well as other Boolean expressions:
create or replace function gt_zero ( p int )
return boolean as
begin
return p > 0;
end;
/
select
gt_zero ( -1 ) minus_one,
gt_zero ( 1 ) one,
0 > 0 zero;
MINUS_ONE ONE ZERO
----------- ----------- -----------
FALSE TRUE FALSE
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:
__
<ReportParameter Name="paramPickupOrders">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<Prompt>Pickup orders?</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
<Label>NO</Label>
</ParameterValue>
<ParameterValue>
<Value>1</Value>
<Label>YES</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
...
<Query>
<DataSourceName>Gmenu</DataSourceName>
<QueryParameters>
<QueryParameter Name=":paramPickupOrders">
<Value>=Parameters!paramPickupOrders.Value</Value>
</QueryParameter>
<CommandText>
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:
__
</ReportParameter>
<ReportParameter Name="paramFilterOrdersWithNoLoad">
<DataType>Boolean</DataType>
<DefaultValue>
<Values>
<Value>false</Value>
</Values>
</DefaultValue>
<Prompt>Only orders with no load?</Prompt>
</ReportParameter>
...
<Tablix Name="tablix_dsMyData">
<Filters>
<Filter>
<FilterExpression>
=(Parameters!paramFilterOrdersWithNoLoad.Value=false)
or (Parameters!paramFilterOrdersWithNoLoad.Value=true and Fields!LOADNUMBER.Value=0)
</FilterExpression>
<Operator>Equal</Operator>
<FilterValues>
<FilterValue DataType="Boolean">=true</FilterValue>
</FilterValues>
</Filter>
</Filters>
How about using an expression which evaluates to TRUE (or FALSE)?
select get_something('NAME', 1 = 1) from dual
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.
© 2022 - 2024 — McMap. All rights reserved.
CASEstatement. The only sane answer is to upgrade your database to PostgreSQL, which deals with booleans in SQL statements beautifully. – Begley