.NET PL/SQL ORA-01001: invalid cursor

Question

I'm using "ASP.NET Framework" with "Oracle.ManagedDataAccess.Client" to get result from ORACLE Procedure, but I get in some times error "ORA-01001: invalid cursor" or "ORA-01006: bind variable does not exist" for the same parameter in random tried, I can not find the issue because it's not allows occurred,

I'm try to increases number cursor open to 5000, but still issue occurred

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:

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;

Can you provide help?

Answer 1

Instead of using type REF_SELECT- whatever that type is, i would recommend to use SYS_REFCURSOR instead.

Also dont forget to open refcursor when Other exception occurs. Thats why sometimes you get invalid cursor.

Explanation: If a cursor or cursor variable is not open, referencing it raises the predefined exception INVALID_CURSOR.

You can also remove ELSIF and use ELSE instead. Its more guaranteed that it enters any part of code during execution, so you will be able to reference that cursor from C#.

I already corrected everything for you, try the following code:

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;