Oracle 11g Stored Proccedure won't talk to our ColdFusion code.

WELL THAT SURE TURNED OUT TO BE CRAP!

ORIGINAL MESSAGE:

We are attempting to execute a stored procedure with this piece of code.

<cfstoredproc returncode="yes" datasource="purload" procedure="co_error.check_records" debug="yes">
     <cfprocparam type="in" cfsqltype="cf_sql_decimal" value="2010">
</cfstoredproc>

As you can see, it is a pretty small <cfstoredproc...> block.  Executing it returns this error.

[Macromedia][Oracle JDBC Driver]Unhandled sql type

 

The error occurred in D:\playground\warren\PpurComponents.cfc: line 49

Called from D:\playground\warren\ppur_file_import.cfm: line 53

Called from D:\playground\warren\PpurComponents.cfc: line 49

Called from D:\playground\warren\ppur_file_import.cfm: line 53

Here is what I hope is the relevant portion of a 1400 line stored procedure located in Oracle.  We don't get to refactor the stored procedure until phase two of this project.

PROCEDURE Check_records(p_year IN NUMBER)
 AS v_use_no               NUMBER(8);
 v_error_code           BINARY_INTEGER := 0;
 v_error_type           VARCHAR2(20);
...

This project is to replace a 15 year old Perl program that used to call this procedure with this code.

open(SQL_OUTPUT, "sqlplus -S ops\$xxxxx/yyyyyy\@dev11ge \@check_errors $year |")

That executed this SQL file.

* Run the error checking procedures
 */

SET pause off
SET verify off
SET TERMOUT ON
SET FEEDBACK OFF
SET document off
SET serveroutput ON size 1000000 format word_wrapped

WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK

EXECUTE Co_error.Check_records(&1);

show errors

EXIT 0

Can anybody provide some insight on why this won't work form our CFML?

TIA

Ian

Message was edited by: ilssac  MY GOOD LORD DID THAT TAKE A LOT OF EFFORT IN ORDER TO MAKE A READABLE MESSAGE!

Message was edited by: ilssac: After fighting with Jive I forgot to add - if we take out the returncode="yes" parameter from the <cfstoredproc...> tag, CF will not throw an exception, but the stored procedure still does not execute.

More Information.

Our stored procedure is now working.    Our DBA determined that the call  was working, but the procedure was waiting, because the previous  <cfquery..> INSERT block was not committed.  But why would that  be?  I've never had to explicity commit my INSERTS using  <cfquery...> before.  Is this something common?  Is is something  in the Database or drivers or just Oracle 11g?

Placing a commit <cftransaction...> block around they  <cfquery..> resolved the issue.  But I have never had to do that  before.