1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Getting SQL Exception ORA-08103: object no longer exists in java

Discussion in 'Oracle Development General' started by Abhi_aec, Feb 26, 2015.

  1. Abhi_aec

    Abhi_aec Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Below is my procedure signature:
    `PROCEDURE sp_trx(i_arr_Sust IN T_TAB_SUST,
    o_locator_map OUT SYS_REFCURSOR,
    o_pkid_map OUT SYS_REFCURSOR,
    o_error OUT VARCHAR2)`

    And below is my ref cursor inside procedure:


    OPEN o_locator_map FOR
    SELECT c_uuid,
    c_id,
    r_locator,
    TO_CHAR(cj_creation_date, g_dt_format) c_date,
    TO_CHAR(cj_last_modified_date, g_dt_format) cj_last_modified_date,
    version_number
    FROM tmp_locator_map;

    Below is data type in oracle:
    c_uuid-->VARCHAR2(50 BYTE), c_id--> NUMBER, r_locator--> VARCHAR2(10 BYTE)

    And below is my Java process for procedure:

    String insertStoreProc = "{call PKG_LOADER.sp_trx(?,?,?,?)}";
    CallableStatement callableStatement = con.prepareCall(insertStoreProc);
    callableStatement.setObject(1, returninParam, 2003);
    callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
    callableStatement.registerOutParameter(3, OracleTypes.CURSOR);
    callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR);
    callableStatement.execute();
    Object obj_recordLoc = callableStatement.getObject(2);
    ResultSet rset =((OracleCallableStatement) callableStatement).getCursor(2);

    while (rset.next()){
    String c_uuid = rset.getString(1);
    }

    Now Problem is i'm getting below mention exception at **rset.next()**:

    java.sql.SQLException: ORA-08103: object no longer exists

    Kindly suggest.
    Thanks in advance
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello
    I believe directly we can call plsql procedure from java using just procedure name . May I know what is the purpose of PKG_LOADER you are using to call the procedure

    Below is an example to call pl_sql procedure:

    Code (SQL):
      callstmt = con.prepareCall("{call set_employee_salary(?, ?)}");
    here set_employee_salary is a procedure
     
  3. Abhi_aec

    Abhi_aec Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    If I tried directly then getting below exception:
    java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00201: identifier 'sp_trx' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    And I think there is no problem to use procedure with package.
    One more thing i'm using it in multi threading environment.
    Now problem is with only ref cursor fetching.
    Kindly let me know if there any alternate to fetch ref cursor.
     
  4. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    I believe we can call a procedure in the package . Is PKG_LOADER is your package name?


    It seems the issue is in your procedure code .

    If it is not so long you could paste your procedure here . we would try to find out the issue
     
  5. Abhi_aec

    Abhi_aec Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    {call PKG_LOADER.sp_trx(?,?,?,?)} Here PKG_LOADER is package and sp_trx is procedure.
    Procedure is too long, it's not possible to share. And there is no problem with procedure believe me.
     
  6. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
  7. Abhi_aec

    Abhi_aec Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I have edited my last reply, actually it is sp_trx instead of SP_CJM_BULK_TRX, it was typo mistake
     
  8. Abhi_aec

    Abhi_aec Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    It's working fine in non-multithreading environment.
    But when I switch to Multi-Threading environment, problem get start. When I call this procedure in multithreading, we are not able to find anything in resultset. Even I have tried with Spring JdbcTemplate and SimpleJdbcCall as well, but not able to get any break through.