Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01001 invalid cursor

1015020Jun 13 2013

Hello,

I have an "ora-01001 invalid cursor " issue I am not able to solve.

In our production environment, we have three application server and it's pointing the single Database.

I am receiving the "ora-01001 invalid cursor" error while calling the follwoing procedure through the jdbc,

Procedure

CREATE OR REPLACE

PROCEDURE "GET_CUSTOMER_DETAILS"(

    p_ACC_NUM IN VARCHAR2 ,

    o_PRODUCT_CODE OUT VARCHAR2 ,

    o_PRODUCT_TYPE OUT VARCHAR2 ,

    o_NATIONALITY OUT VARCHAR2 ,

    o_CUSTOMER_CAT OUT VARCHAR2 ,

    o_SERVICE_STATUS OUT VARCHAR2 ,

    o_CURR_PLAN OUT VARCHAR2 ,

    o_ERROR_CODE OUT NUMBER ,

    o_ERROR_MSG OUT VARCHAR2 )

AS

  v_count         INT;

  v_accNum        VARCHAR2(50);

  v_Product_Desc  VARCHAR2(50);

  v_IPhone_Status VARCHAR2(50);

  v_BB_Status     VARCHAR2(50);

  v_now           DATE;

BEGIN

  o_CURR_PLAN := 0;

  SELECT PRODUCT_DESC,

    PRODUCT_NAME,

    IPHONE_STATUS,

    BB_STATUS,

    PARTY_NATIONALITY,

    CUSTOMER_CAT,

    SERVICE_STATUS,

    new_postpaid_flag

  INTO v_Product_Desc,

    o_PRODUCT_TYPE,

    v_IPhone_Status,

    v_BB_Status,

    o_NATIONALITY,

    o_CUSTOMER_CAT,

    o_SERVICE_STATUS,

    o_CURR_PLAN

  FROM tbl_crm_custmaster

  WHERE account_number  = p_ACC_NUM

  AND rownum            = 1;

  IF (v_Product_Desc    = 'WS' AND v_IPhone_Status = 'N' AND v_BB_Status = 'N') THEN

    o_PRODUCT_CODE     := 'WP'; --General Prepaid

  elsif (v_Product_Desc = 'WS' AND v_IPhone_Status = 'N' AND v_BB_Status = 'Y') THEN

    o_PRODUCT_CODE     := 'WB'; --BlackBerry Prepaid

  elsif (v_Product_Desc = 'WS' AND v_IPhone_Status = 'Y' AND v_BB_Status = 'N') THEN

    o_PRODUCT_CODE     := 'WI'; --IPhone Prepaid

  elsif (v_Product_Desc = 'WS' AND v_IPhone_Status = 'Y' AND v_BB_Status = 'Y') THEN

    o_PRODUCT_CODE     := 'WP'; --General Prepaid

  elsif (v_Product_Desc = 'GS' AND v_IPhone_Status = 'N' AND v_BB_Status = 'N') THEN

    o_PRODUCT_CODE     := 'GP'; --General Postpaid

  elsif (v_Product_Desc = 'GS' AND v_IPhone_Status = 'N' AND v_BB_Status = 'Y') THEN

    o_PRODUCT_CODE     := 'GB'; --BlackBerry Postpaid

  elsif (v_Product_Desc = 'GS' AND v_IPhone_Status = 'Y' AND v_BB_Status = 'N') THEN

    o_PRODUCT_CODE     := 'GI'; --IPhone Postpaid

  elsif (v_Product_Desc = 'GS' AND v_IPhone_Status = 'Y' AND v_BB_Status = 'Y') THEN

    o_PRODUCT_CODE     := 'GP'; --General Postpaid

  elsif (v_Product_Desc = '2P' OR v_Product_Desc = '3P') THEN

    o_PRODUCT_CODE     := 'EL'; --Landline associated with E-Life

  elsif (v_Product_Desc = 'VO') THEN

    o_PRODUCT_CODE     := 'GL'; --Landline Postpaid

  elsif (v_Product_Desc = 'FN') THEN

    o_PRODUCT_CODE     := 'WL'; --Landline Prepaid

  elsif (v_Product_Desc = 'HI') THEN

    o_PRODUCT_CODE     := 'IN'; --Landline Prepaid

  elsif (v_Product_Desc = 'EV') THEN

    o_PRODUCT_CODE     := 'EV'; --Landline Prepaid

  END IF;

  o_ERROR_CODE := 0;

  o_ERROR_MSG  := 'SUCCESS';

EXCEPTION

WHEN NO_DATA_FOUND THEN

  o_ERROR_CODE := -1;

  o_ERROR_MSG  := 'NO REOCRDS FOUND';

WHEN OTHERS THEN

  o_ERROR_CODE := SQLCODE;

  o_ERROR_MSG  := SQLERRM;

END;

Java Code

public HashMap<String, String> getCustomerDetails(String acctNo, SCESession mySession){

  ITraceInfo trace = mySession.getTraceOutput();

  dataSource = (BasicDataSource)mySession.getProperty(DATASRC_IVR_DB);

  HashMap<String, String> returnhash = new HashMap<String, String>();

  //trace.writeln(ITraceInfo.TRACE_LEVEL_INFO, "Account number :"+acctNo.substring(acctNo.length()-4) );

  try

  {

  //logger.debug(callId+" The input is:"+hm);

  start=System.currentTimeMillis();

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO, "Start Time of GET_CUSTOMER_DETAILS="+start);

  con=dataSource.getConnection();

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO, "Data source connection established");

  proc = con.prepareCall("{ call GET_CUSTOMER_DETAILS(?, ?, ?, ?, ?, ?, ?, ?, ?) }");

 

  if(acctNo !=null && !acctNo.trim().isEmpty()){

  proc.setString(1, acctNo);

  }else{

  proc.setNull(1,  OracleTypes.VARCHAR);

  }

  proc.registerOutParameter(2, Types.VARCHAR);

  proc.registerOutParameter(3, Types.VARCHAR);

  proc.registerOutParameter(4, Types.VARCHAR);

  proc.registerOutParameter(5, Types.VARCHAR);

  proc.registerOutParameter(6, Types.VARCHAR);

  proc.registerOutParameter(7, Types.VARCHAR);

  proc.registerOutParameter(8, Types.INTEGER);

  proc.registerOutParameter(9, Types.VARCHAR);

  try {

  if(!QueryTimeout.equalsIgnoreCase(NO))

  proc.setQueryTimeout(Integer.parseInt(QueryTimeout));

  else

  proc.setQueryTimeout(_2s);

  } catch (NumberFormatException e) {

  proc.setQueryTimeout(_2s);

  }

  trace.writeln(ITraceInfo.TRACE_LEVEL_DEBUG,"Time out="+QueryTimeout);

  proc.execute();

  returnhash.put(ERROR_CODE, proc.getString(8));

  returnhash.put(ERROR_MSG, proc.getString(9));

  if(returnhash.get(ERROR_MSG).equalsIgnoreCase(DB_SUCCESS_MSG) || returnhash.get(ERROR_CODE).equalsIgnoreCase(DB_SUCCESS_CODE)){

  returnhash.put(ACCOUNT_NUMBER, acctNo);

  //returnhash.put(PHONE_NUMBER, rs.getString(2));

  returnhash.put(PROD_CODE, proc.getString(2));

  returnhash.put(PRODUCT_TYPE, proc.getString(3));

  returnhash.put(NATIONALITY, proc.getString(4));

  returnhash.put(CUSTOMER_CAT, proc.getString(5));

  returnhash.put(SERVICE_STATUS, proc.getString(6));

  returnhash.put(CURR_PLAN, proc.getString(7));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,ACCOUNT_NUMBER+":"+ acctNo);

trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,PROD_CODE+":" + proc.getString(2));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,PRODUCT_TYPE+":" + proc.getString(3));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,NATIONALITY+":" + proc.getString(4));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,CUSTOMER_CAT+":" + proc.getString(5));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,SERVICE_STATUS+":" + proc.getString(6));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,CURR_PLAN+":" + proc.getString(7));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,ERROR_CODE+":"+proc.getString(8));

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO,ERROR_MSG+":"+proc.getString(9));

  }else{

  trace.writeln(ITraceInfo.TRACE_LEVEL_DEBUG, "No records found");

  }

  end=System.currentTimeMillis();

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO, "end Time of GET_CUSTOMER_DETAILS="+end);

  trace.writeln(ITraceInfo.TRACE_LEVEL_INFO, "Total Time of GET_CUSTOMER_DETAILS="+(end-start)+"ms");

  }

  catch (SQLException e)

  {

  returnhash.put(ERROR_CODE, "03");

  returnhash.put(ERROR_MSG, e.getMessage());

  trace.writeln(ITraceInfo.TRACE_LEVEL_ERROR, "SQL Exception"+e);

  e.printStackTrace();

  }

  finally{

  try{

  closeResult(rs);

  closeCallableStmt(proc);

  closeConnection(con);

  }catch(SQLException e){

  returnhash.put(ERROR_CODE, "03");

  returnhash.put(ERROR_MSG, e.getMessage());

  trace.writeln(ITraceInfo.TRACE_LEVEL_ERROR, "SQL Exception while closing the Connection "+e);

  }

  if(trace != null){

  trace = null;

  }

  if(dataSource != null){

  dataSource = null;

  }

  }

  return returnhash;

  }


I couldn't reproduce this issue in sqlplus, it only happens from jdbc calls from the application server 2 and application server 3, and repeating the same call usually works (but sometimes fails couple of times before succeeding). I have not used any cursor in the procedure. Please help me on this issue.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 11 2013
Added on Jun 13 2013
0 comments
274 views