Skip to main content
B
Barry_Andre
Inspiring
July 10, 2006
Answered

Passing boolean parameters to Oracle

  • July 10, 2006
  • 1 reply
  • 3903 views
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.
This topic has been closed for replies.
Correct answer Barry_Andre
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

1 reply

P
paross1
Participating Frequently
July 10, 2006
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
B
Barry_AndreAuthorCorrect answer
Inspiring
July 11, 2006
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices