. net PL/SQL ORA-01001:无效游标

Question

我用的是"ASP。净Framework"与"Oracle.ManagedDataAccess.Client"从ORACLE过程中获得结果，但我在某些时候得到错误"ORA-01001:无效游标";ORA-01006:绑定变量不存在;对于相同的参数在随机尝试，我找不到问题，因为它不允许发生，

我尝试将数字游标打开到5000，但仍然发生问题

public void checkStdHasExam(int studentId, int subjectId)
{
OracleConnection conn = DatabaseConnection.DBconn();
OracleCommand cmd = new OracleCommand("API_PACKAGE.CHECK_STD_LINKED_MATH", conn);
cmd.Parameters.Add(new OracleParameter(":P_STUDENT_ID", studentId));
cmd.Parameters.Add(new OracleParameter(":P_REF_CURSOR", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (OracleDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var hasExam = Convert.ToInt32(reader["math_linked"]);
}
}
}
catch (Exception ex)
{
Utils.CreateLOG("checkStdHasExam", ex, $"{studentId},{subjectId}");
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}

ORACLE过程:

PROCEDURE CHECK_STD_LINKED_MATH(P_STUDENT_ID NUMBER, P_REF_CURSOR OUT REF_SELECT)
AS   
V_HOMEROOM      VARCHAR2(500);
V_LINKED_MATH   NUMBER;     
V_LOG_ID        NUMBER;
V_MESSAGE_LOG   VARCHAR2(200);
BEGIN 
SELECT HOMEROOM INTO V_HOMEROOM FROM  SUBSCRIBERS WHERE ID =  P_STUDENT_ID;   
SELECT COUNT(1) INTO V_LINKED_MATH FROM TEACHER_HOMEROOMS WHERE SUBJECT_ID = 3 AND HOMEROOM = V_HOMEROOM;

IF V_LINKED_MATH > 0 THEN 
OPEN P_REF_CURSOR FOR 
SELECT 1 AS MATH_LINKED FROM DUAL;
ELSIF  V_LINKED_MATH <= 0 THEN
OPEN P_REF_CURSOR FOR 
SELECT 0 AS MATH_LINKED FROM DUAL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN  
OPEN P_REF_CURSOR FOR 
SELECT 0 AS MATH_LINKED FROM  DUAL;  
WHEN OTHERS THEN                        
V_LOG_ID := DBF_GENERATE_ID('SYSTEM_API_LOG');
V_MESSAGE_LOG := SQLERRM;
INSERT INTO SYSTEM_API_LOG VALUES(V_LOG_ID, SYSDATE, 'CHECK_STD_LINKED_MATH',V_MESSAGE_LOG, '');  
END;

你能提供帮助吗?

Answer 1

不使用类型REF_SELECT-无论该类型是什么，我建议使用SYS_REFCURSOR代替。

也不要忘记打开refcursor时，Other异常发生。这就是为什么有时你会得到invalid cursor。

解释:如果游标或游标变量未打开，引用它将引发预定义的异常INVALID_CURSOR。

您还可以删除ELSIF并使用ELSE代替。它在执行过程中更有保证地进入代码的任何部分，因此您将能够从c#中引用游标。

我已经为你更正了一切，试试下面的代码:

PROCEDURE CHECK_STD_LINKED_MATH(P_STUDENT_ID NUMBER, P_REF_CURSOR OUT SYS_REFCURSOR)
AS   
V_HOMEROOM      VARCHAR2(500);
V_LINKED_MATH   NUMBER;     
V_LOG_ID        NUMBER;
V_MESSAGE_LOG   VARCHAR2(200);
BEGIN 
SELECT HOMEROOM INTO V_HOMEROOM FROM  SUBSCRIBERS WHERE ID =  P_STUDENT_ID;   
SELECT COUNT(1) INTO V_LINKED_MATH FROM TEACHER_HOMEROOMS WHERE SUBJECT_ID = 3 AND HOMEROOM = V_HOMEROOM;

IF V_LINKED_MATH > 0 THEN 
OPEN P_REF_CURSOR FOR 
SELECT 1 AS MATH_LINKED FROM DUAL;
ELSE
OPEN P_REF_CURSOR FOR 
SELECT 0 AS MATH_LINKED FROM DUAL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN  
OPEN P_REF_CURSOR FOR 
SELECT 0 AS MATH_LINKED FROM  DUAL;  
WHEN OTHERS THEN 
OPEN P_REF_CURSOR FOR 
SELECT 0 AS MATH_LINKED FROM  DUAL;       
V_LOG_ID := DBF_GENERATE_ID('SYSTEM_API_LOG');
V_MESSAGE_LOG := SQLERRM;
INSERT INTO SYSTEM_API_LOG VALUES(V_LOG_ID, SYSDATE, 'CHECK_STD_LINKED_MATH',V_MESSAGE_LOG, '');  
END;