PL/SQL Convert String To Boolean

Question

I have an EXT JS page which sends a form with some values. The values are all sent in string and some sent as boolean. It is calling a PL/SQL procedure where the params are all varchar. For some reason when the form is submitted, even though some values are sent as boolean, they can not be received by the procedure as boolean. All the values received from the procedure are varchar; and must be otherwise it crashes.

So I am sending a boolean from the form to the procedure. When it gets to the procedure it is now a varchar. How can I convert this back to a boolean?

Any help at all appreciated, I feel like I'm doing something wrong here. I don't understand why it is receiving this as a varchar.

Solution

You are right that there is no Oracle built-in string-to-boolean function, but you could easily create one yourself:

create or replace function to_boolean
  ( p_string varchar2
  ) return boolean
is
begin
  return
    case upper(p_string) 
      when 'TRUE' then true
      when 'FALSE' then false
      else null
      end;
end;

(The "else null" is redundant but I put it there to remind you that if upper(p_string) is anything other than 'TRUE' or 'FALSE' then the function will return null).

You could of course enhance the function to treat other string values as true or false also e.g. 'T', 'YES', ...

Comments

[Tony Andrews]

I don't understand: what is converting the parameter from Boolean to varchar? Can you post some code?

[user1375026]

It's not me that's doing it, I believe it's in the background. I'm sending a boolean, somehow this is converted to a varchar when sent. I found a work around: In the procedure I will just say if value = "true" then set a boolean value to true. I think I will just go with this method because I don't think I can control the conversion taking place and I don't think it's possible to convert a varchar to a boolean. Thanks anyway Tony.