Reputation: 49
I am writing unit tests for an existing procedure which has boolean parameters. As the tests run, they will store the associated parameter values in a table. I want to use variables for setting the parameter columns in the table and to pass to the procedure. Is there a way to go from an integer (what I'll set in my table) to the procedure (which requires boolean values)?
I've tried passing 1/0 values and the strings "TRUE" and "FALSE" to the procedure. I've tried making my table use a boolean datatype for the relevant columns. I've tried CASTing. I've tried using a SELECT INTO with CASE statement to set a different boolean-type variable based on the value of the integer-type variable.
CREATE TABLE UNIT_TEST_RESULTS (
case VARCHAR2(50)
,includeLines NUMBER(1) --this will hold the value of i_includeLines below
,result VARCHAR2(4)
);
CREATE OR REPLACE PROCEDURE X_UNIT_TEST AS
i_includeLines NUMBER(1)
BEGIN
i_includeLines:=0;
X_THING_TO_TEST(includeLinesBool=>i_includeLines);
/*...analyze output...*/
INSERT INTO UNIT_TEST_RESULTS(case,includeLines,result)
VALUES ('test',i_includeLines,'fail'); COMMIT;
END X_UNIT_TEST;
Upvotes: 1
Views: 1794
Reputation: 143163
There's no Boolean in your code so - here's how I understood the question:
You have a procedure which accepts parameter that is Boolean:
SQL> create or replace procedure p_test
2 (par_bool in boolean)
3 is
4 begin
5 null;
6 end;
7 /
Procedure created.
SQL>
You want to call it; there's some number variable (l_int
in my example) and you'd want to convert it to Boolean and pass as such to the p_test
procedure. Here's how:
SQL> declare
2 l_int integer := 1;
3 begin
4 p_test(case when l_int = 1 then true else false end);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
Upvotes: 0
Reputation: 16001
There is no implicit conversion from numbers or strings to Boolean, but a simple true/false expression like i_includeLines = 1
evaluates to Boolean true
if i_includeLines
has the value 1
, so you could call your procedure like this:
x_thing_to_test(includeLinesBool => i_includeLines = 1);
That will pass true
if i_includeLines
is 1, false
if it is not 1, else null
.
If you need to treat null as false
then you'll need a case
/coalesce
/nvl
or similar expression.
Upvotes: 0
Reputation: 231891
I'd probably do something like
i_includeLines NUMBER(1)
b_includeLines boolean;
BEGIN
i_includeLines:=0;
b_includeLines := (case i_includeLines when 1 then true else false end);
X_THING_TO_TEST(includeLinesBool=>b_includeLines);
or
b_includeLines boolean;
i_includeLines integer;
BEGIN
b_includeLines:= false;
X_THING_TO_TEST(includeLinesBool=>b_includeLines);
/*...analyze output...*/
i_includeLines := case when b_includeLines = true then 1 else 0 end;
INSERT INTO UNIT_TEST_RESULTS(case,includeLines,result)
VALUES ('test',i_includeLines,'fail');
COMMIT;
Obviously, either of these case
statements could (and should) get encapsulated into a function assuming you are going to call them regularly.
I would suggest that you not use the reserved word case
for a column name-- that's going to bite you at some point. I'm also not a fan of the Hungarian notation for variable names but that's more of a religious debate...
Upvotes: 3