.Net Oracle ORA-24338“语句句柄未执行”一个存储过程中的错误和一些错误
我有以下存储过程
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;
并且
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;
我使用以下代码从应用程序调用它们:
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
在本例中,WEB_AC SP
始终执行 sp WEB_UPDOC1C6
我有两个问题这。
第一个问题:在应用程序中的某个时刻,我将(视觉基本函数的)valor
参数作为带有空格的字符串,类似于“some string带空格”。发生这种情况时,存储过程不会更新表。如果我直接在数据库中执行 SP(使用 SQL Developer)一切正常。我知道这与字符串缺少一些引号(')有关,但我还没有使它工作。对此有一些想法吗?
第二个问题:有时,在调试应用程序时,如果我中断执行,我每次尝试都会开始收到ORA-24338“语句句柄未执行”错误几个小时再次执行它。我认为这与公开交易有关。但老实说,由于我是 Oracle 的新手,我真的不知道问题出在哪里。
你能帮助我吗?
更新:我已经找到了ORA-24338的真正原因。是另一个 SP 导致了错误。当我找到其他问题的解决方案时,我会将其全部发布在这里。
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 problem: 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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我建议您将 WHEN OTHERS 子句替换为专门指定您期望的错误的子句,或者无论如何在处理错误后引发错误。 WHEN OTHERS 有点矛盾,因为它因隐藏真正的问题而臭名昭著。
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.