2 Replies Latest reply on Jul 26, 2010 8:32 AM by ilssac

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

    ilssac Level 5

      WHY IS JIVE SAYING THIS IS A BLANK MESSAGE?????

       

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

       

      
      
      
      
       
      
       
      
      
      
      [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.
      
      
           
      
      
       
      
       
      
        • 1. Re: Oracle 11g Stored Proccedure won't talk to our ColdFusion code.
          ilssac Level 5

          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.

          • 2. Re: Oracle 11g Stored Proccedure won't talk to our ColdFusion code.
            ilssac Level 5

            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.