.Net Oracle ORA-24338 'statement handle not executed' error and some error in one stored Procedure

Question

Hi,
I have the following Stored Procedures

create or replace PROCEDURE WEB_AC
(
  v_iDocid IN NUMBER DEFAULT NULL ,
  v_valor IN VARCHAR2 DEFAULT NULL ,
  v_campo IN VARCHAR2 DEFAULT NULL ,
  v_error  OUT NUMBER
)
AS
v_campoid NUMBER(5,0);
v_tipodato VARCHAR2(50);
v_DOCTYPE NUMBER;
v_tabla VARCHAR2(50);
v_procedure VARCHAR2(70);
BEGIN
  v_error:= 0;
  IF v_valor IS NULL
    OR v_valor IS NULL
    OR LENGTH(TRIM(v_valor)) = 0 THEN
       BEGIN
           v_error:= 3;

       END;
  else   
   Begin
      bEGIN
        SELECT campoid,
            doctype,
          tipodato
        INTO v_campoid,
          v_DOCTYPE,
          v_tipodato
        FROM TiposDocumento t
            , DIGITALIZAMAIN d
            , CatCamposDocumento c
        where
            c.tabla=t.tabla and
         nombre=v_campo and
         doctype = TipoDocumentoID and
         docid = v_iDocid AND 
         Mostrar = 1;
        EXCEPTION
          WHEN OTHERS THEN
            v_campoid := 0;
      END;
    --select @campoid
       IF v_campoid != 0 THEN
         Begin
              EXECUTE IMMEDIATE 'BEGIN ABANCE3.WEB_UPDOC' || TRIM(TO_CHAR(v_DOCTYPE )) || 'C' || TRIM(TO_CHAR(v_campoid)) ||'(' ||
             TO_CHAR (v_iDocid)||' , '||CHR(39)||v_valor||CHR(39)||',:2);END;'  
                USING out v_error;

      END;
    END IF;
    end;
  end if;  
 END;

And

 create or replace PROCEDURE WEB_UPDOC1C6(v_idreg NUMBER,v_valor VARCHAR2,v_temp OUT NUMBER) 
 AS
   v_sys_error NUMBER := 0;
    BEGIN
      BEGIN
       SELECT count(*)
         INTO v_sys_error
         FROM DOC1
         where DOCID = v_idreg;
         EXCEPTION WHEN OTHERS THEN v_sys_error:=0;
       END;


       IF v_sys_error > 0 THEN
         BEGIN
           BEGIN
             UPDATE DOC1
               SET DESCRIPCION = v_valor
               WHERE  DOCID = v_idreg;
              EXCEPTION WHEN OTHERS THEN v_sys_error:=0;
          END;
         IF v_sys_error = 0 THEN v_temp:=0 ;
         ELSE v_temp:=1 ;
         END IF;
       END;
     END IF;
   END;

and I'm calling them from an Application with this code:

 Friend Function ActualizaCampos(ByVal iDocID As Long, ByVal valor As String, ByVal Campo As String, ByVal ProyectoID As Integer) As String
         Dim mstrCS as String = "Here goes the connection String to my server"
         Dim db As Database
         Dim dbCW As DbCommand
         Dim iValor As String = "0"

    Select Case Me.TipoBD
             Case GlobalDef.eTipoBD.Oracle
                 db = New OracleDatabase(mstrCS)
                 dbCW = db.GetStoredProcCommand(WEB_AC, iDocid, valor, Campo, 0)
                 db.ExecuteNonQuery(dbCW)
             Case GlobalDef.eTipoBD.SQLServer
                 db = New SqlDatabase(mstrCS)
                 dbCW = db.GetStoredProcCommand(WEB_AC, iDocid, valor, Campo)
                 iValor = db.ExecuteScalar(dbCW).ToString()
         End Select

         Return iValor
     End Function

In this example the WEB_AC SP always execute the sp WEB_UPDOC1C6

I have two problems with this.

First one: At some point in the application I have the valor parameter (of the visual Basic Function) as a string with spaces that is something like "some string with spaces". When this happens, the stored procedure don't update the table. If I execute the SP directly in the DB (with SQL Developer) all works fine. I know it has something to do with the string missing some quotes(') but I haven't make it work yet. Some ideas on this?

Second problem: Sometimes, when debuging the application, if I interrupt the execution, I start getting the ORA-24338 'statement handle not executed' error for hours every time I try to execute it again. I believe it has something to do with an open transaction. But honestly, as I'm new in working with Oracle, I really have no idea what the problem could be.

Can you help me?

UPDATE: I have found the ORA-24338 real reason. It was another SP that was causing the error. When i found the solution to my other problem I'll post it all here.

Answer 1

I'd suggest that you replace your WHEN OTHERS clause with one that specifically names the erros that you are expecting, or you raise the error after handling it anyway. WHEN OTHERS is a bit contraversial as it is notorious for hiding the real problem.