CODEWITHSUNDEEP
javaoracle-databasehibernatestored-procedures
Shahnwaz Alam
Shahnwaz Alam

Reputation: 23

Cursor is closed

I am using struts framework. I am getting a "cursor is closed" error. I have checked my storedprocedure and it's working well in oracle but still I am getting the "cursor is closed" error.

    Caused by: 
java.sql.SQLException: Cursor is closed.
    at oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:323)
    at oracle.jdbc.driver.ResultSetAccessor.getObject(ResultSetAccessor.java:85)
    at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:1401)

Can you help me understand what caused this?

Upvotes: 1

Views: 28610

Answers (4)

Fazil
Fazil

Reputation: 351

Hope You may returning back Procedure out parameter as cursor to the calling place but you may missed to open/add the out param cursor (OPEN RESULT FOR SELECT * FROM TABLE_NAME) inside the procedure. 

Example:
PROCEDURE TEST(RESULT OUT SYS_REFCURSOR) 
AS
BEGIN
  --your all codes
  OPEN RESULT FOR  SELECT * FROM TABLE_NAME;
END TEST;

Upvotes: 0

Keshan Fernando
Keshan Fernando

Reputation: 367

Initially a REF_CURSOR is at closed state until you open the cursor for a particular SQL query. You may get this Cursor is closed SQL error when you have never opened the cursor due to some conditions before opening the cursor. If that conditions are not fulfilled, then the cursor is never getting opened if you don't open the cursor for some dummy values purposely. Following is an example:

IF condition = TRUE THEN
    OPEN CURSOR cursor_name FOR
    SELECT * FROM table_name;
END IF;

But in here, condition is FALSE. Therefore, the procedure returns a non-opened cursor. In situations like this, I prefer to catch this particular exception from calling program. For example, following is a Java code to catch particular type of exception and be silent.

try
{
    cursor = (ResultSet) stmt.getObject(x);
}
catch(SQLException e)
{
    if (!e.getMessage().toLowerCase().contains("cursor is closed")) {
        e.printStackTrace();
    }
}

instead of doing

try
{
    cursor = (ResultSet) stmt.getObject(x);
}
catch(SQLException e)
{
    e.printStackTrace();
}

Upvotes: 0

Gaurav Soni
Gaurav Soni

Reputation: 6338

Since you have not provided the code, but according to my assumption, you are returning a cursor from the procedure by opening it, but at the same time you might be closing the cursor in the same procedure. 

PROCEDURE S_S_TEST( 
  test_OUT OUT OAS_TYPES.REFCURSOR
) 
AS
BEGIN
  OPEN test_OUT FOR      
      SELECT *
      FROM table_p;
   CLOSE test_OUT;
END S_S_TEST;

The client calling the stored procedure is responsible for closing the cursor. Please remove the code: CLOSE test_OUT;

Refer: Cursor is Closed

Upvotes: 2

Vinay
Vinay

Reputation: 1

Some time below issue will come

Ex:

while (rs.next()) {


            registartionServices.add(new RegistrationServices(rs.getString(1), rs.getString(2), rs.getString(3),
                    rs.getString(4), rs.getString(5), rs.getString(7), rs.getString(6), rs.getString(9)));
            noti[i] rs.getString(1);
            //System.out.println("amountList-" + rs.getString(7));
            //amountList.add(rs.getString(7));
            //serviceList.add(rs.getString(6));
            //serviceListAR.add(rs.getString(6));
            i++;
        }

In above example (rs.getString(1) ) is trying to access two times.

Instead of this assign (rs.getString(1)) to one variable and use that value.

Ex:

while (rs.next()) {
                notid=rs.getString(1);
                registartionServices.add(new RegistrationServices(notid, rs.getString(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(7), rs.getString(6), rs.getString(9)));
                noti[i] =notid;
                //System.out.println("amountList-" + rs.getString(7));
                //amountList.add(rs.getString(7));
                //serviceList.add(rs.getString(6));
                //serviceListAR.add(rs.getString(6));
                i++;
            }

Hope you got the answer.

Upvotes: 0

Related Questions