On the cf-talk mailing list there was a thread about ColdFusion calling an Oracle stored procedure that has a BOOLEAN as the data type of one of its input arguments. Looks like ColdFusion does not have a CF_SQL_BOOLEAN as a valid value for the cfsqltype argument in the cfprocparam. So, what to do?

First, an example:

scott@eddev> create or replace procedure foo_prc (
  2      param_in in boolean
  3     ,param_out out varchar2
  4  )
  5  as
  6  begin
  7      if param_in then
  8          param_out := 'input is true';
  9      else
 10          param_out := 'input is false';
 11      end if;
 12  end foo_prc;
 13  /

Procedure created.

scott@eddev> declare
  2      param_out_v varchar2(20);
  3  begin
  4      foo_prc (true, param_out_v);
  5      dbms_output.put_line(param_out_v);
  6      foo_prc (false, param_out_v);
  7      dbms_output.put_line(param_out_v);
  8  end;
  9  /
input is true
input is false

PL/SQL procedure successfully completed.

The input parameter param_in in foo_prc is of the datatype boolean. As you can see, calling the procedure and displaying the output completed successfully (as it should) if called from within Oracle.

Now, let’s call the foo_prc procedure from ColdFusion:

<cfstoredproc 
        procedure="foo_prc" 
        datasource="mydsn">
    <cfprocparam 
            type="in" 
            cfsqltype="CF_SQL_VARCHAR" 
            value="true">
    <cfprocparam 
            type="out" 
            cfsqltype="CF_SQL_VARCHAR" 
            variable="cf_param_out">
</cfstoredproc>
<cfoutput>
    foo_proc_output is: #cf_param_out#
</cfoutput>

The above generates an error:

Error Executing Database Query.  
[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: 
line 1, column 7: PLS-00306: wrong number or types 
of arguments in call to 'FOO_PRC' ORA-06550: 
line 1, column 7: PL/SQL: Statement ignored  

So, how can we pass a “boolean” value from ColdFusion to Oracle? Unless you know something I do not know, the answer to this question is simply, you can not. The workaround? You may not like it, but, you will need to change the datatype in Oracle from BOOLEAN to something else. Here is a modified foo_prc that works with ColdFusion:

scott@eddev> create or replace procedure foo_prc (
  2      param_in in varchar2 -- 0/1, Y/N, T/F 
  3     ,param_out out varchar2
  4  )
  5  as
  6  begin
  7      if param_in != '0' and
  8        param_in != 'F' and
  9        param_in != 'N' then
 10          param_out := 'input is true';
 11      else
 12          param_out := 'input is false';
 13      end if;
 14  end foo_prc;
 15  /

Procedure created.

scott@eddev> declare
  2      param_out_v varchar2(20);
  3  begin
  4      foo_prc ('1', param_out_v);
  5      dbms_output.put_line(param_out_v);
  6      foo_prc ('Y', param_out_v);
  7      dbms_output.put_line(param_out_v);
  8      foo_prc ('F', param_out_v);
  9      dbms_output.put_line(param_out_v);
 10  end;
 11  /
input is true
input is true
input is false

PL/SQL procedure successfully completed.

Executing the ColdFusion code above again, will generate the following output:

foo_proc_output is: input is true 

In short, if you are developing an Oracle procedure that you know may be called from ColdFusion, make sure NOT to use BOOLEAN as the data type of any of its parameters.

Related links:
cf-talk thread

---

Possibly related:

• XML configuration files used in ColdFusion MX
• What’s in a Label
• ColdFusion + AJAX = CFAjax
• Read XML from an HTTP POST
• Read The Fabulous Manual

---

Tagged boolean | Post a Comment