Passing boolean parameters to Oracle

[Barry_Andre]

Hello there everyone.
I have just started to work with Oracle stored procedures. I have written a procedure that takes a boolean value as one of its input parameters, but I cannot find out how to get Coldfusion to pass the value in correctly. If I execute the procedure within the Oracle client itself it works OK but CF always gives the error "PLS-00306: wrong number or types of arguments in call to 'PROC_TEST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored", no matter how I try to pass my boolean value. I have tried to use all combinations of CF_SQL_BIT and CF_SQL_INTEGER and "1" and "true" and "TRUE" in the <cfprocparam> tag, but none of them are working. I guess I could change the code to use an integer instead, but using a boolean for this example makes the most sense.

If anyone has any ideas, that would be great.

Regards
Barry.

[paross1]

Th PL/SQL Boolean datatype can be used only within PL/SQL , and is not considered a "valid" datatype by the Oracle RDBMS. In other words, you can't use Boolean outside of PL/SQL. Consider that you can not create a column of type Boolean in an Oracle table -- you can neither SELECT into a Boolean variable nor insert a TRUE or FALSE value directly into a database column.

If you can, you should change the PL/SQL stored procedure parameter to a type that you can pass from ColdFusion, such as bit or int, then "convert" the passed value to a boolean within the procedure if you really need the functionality of a Boolean variable within your PL/SQL.

Phil

[Barry_Andre · Accepted solution]

Hi Phil thanks for replying!
I continued searching Google and came up with the same conclusion you mention. Here are a couple of links:
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#34_05
http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/java.920/a96654/tips.htm#1005343

What a strange situation to be in. On one hand they say that "booleans" can't be used outside of PL/SQL, but what ARE stored procedures if not PL/SQL? Musing about this does not move us forward though I think.

I will change my boolean parameter to be something more SQL (without the PL!) friendly.

Thanks for getting back to me.

Barry