CODEWITHSUNDEEP
oracle-databaseplsql
Avyncentia
Avyncentia

Reputation: 49

How can I convert an integer variable in PLSQL to pass to a boolean procedure parameter?

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

Answers (3)

Littlefoot
Littlefoot

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

William Robertson
William Robertson

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

Justin Cave
Justin Cave

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

Related Questions