I have a problem " ORA-01001 invalid cursor " which I cannot solve. In our production environment, we have three application servers that specify a single database ( Oracle 10g ).
I get the error "ora-01001 invalid cursor" when calling the following procedure via 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 1:
public void requestBegin(SCESession mySession) { // TODO Auto-generated method stub ITraceInfo trace = mySession.getTraceOutput(); UserDetails userDetails = (UserDetails) mySession.getProperty(EtisalatConstants.USER_DETAILS); CallDetailsBean detailsBean=(CallDetailsBean) mySession.getProperty(EtisalatConstants.CALLER_DETAILS_BEAN_OBJECT); DwhEtisalatMethods etisalatMethods=null; HashMap<String, String> response = new HashMap<String, String>(); String hostErrResponse = "", hostErrCode = ""; EtaDbImpl dao = null; try{ //Host Reporting Start Details etisalatMethods=new DwhEtisalatMethods(); etisalatMethods.HostStartDetails(mySession, CommonMethod.generateUniqueId(), LoggingConstants.GET_CALLER_DETAILS_CRM, LoggingConstants.NOT_APPLICABLE, LoggingConstants.DATABASE); dao = new EtaDbImpl(); response = dao.getCustomerDetails(userDetails.getPhoneNumber(), mySession); trace.writeln(ITraceInfo.TRACE_LEVEL_DEBUG,"DB Success:"); hostErrCode = response.get(EtisalatDbConstants.ERROR_CODE); hostErrResponse = response.get(EtisalatDbConstants.ERROR_MSG); trace.writeln(ITraceInfo.TRACE_LEVEL_DEBUG,"ERROR_CODE:"+hostErrCode); trace.writeln(ITraceInfo.TRACE_LEVEL_DEBUG,"ERROR_Response:"+hostErrResponse); if(hostErrResponse.equalsIgnoreCase(EtisalatDbConstants.DB_SUCCESS_MSG) || hostErrCode.equalsIgnoreCase(EtisalatDbConstants.DB_SUCCESS_CODE)){ userDetails.setAccountNumber(response.get(EtisalatDbConstants.ACCOUNT_NUMBER)); //userDetails.setPhoneNumber(response.get(EtisalatDbConstants.PHONE_NUMBER)); userDetails.setProductType(response.get(EtisalatDbConstants.PRODUCT_TYPE)); userDetails.setProductCode(response.get(EtisalatDbConstants.PROD_CODE)); userDetails.setNationality(response.get(EtisalatDbConstants.NATIONALITY)); userDetails.setCustomerCategory(response.get(EtisalatDbConstants.CUSTOMER_CAT)); userDetails.setCurrPlan(response.get(EtisalatDbConstants.CURR_PLAN)); userDetails.setServiceStatus(response.get(EtisalatDbConstants.SERVICE_STATUS)); //Set in Project variable mySession.getVariableField(IProjectVariables.APP_VARIABLES, IProjectVariables.APP_VARIABLES_FIELD_ACCOUNT_NO).setValue(userDetails.getAccountNumber()); mySession.getVariableField(IProjectVariables.APP_VARIABLES, IProjectVariables.APP_VARIABLES_FIELD_PRODUCT_CODE).setValue(userDetails.getProductCode()); mySession.getVariableField(IProjectVariables.APP_VARIABLES, IProjectVariables.APP_VARIABLES_FIELD_PRODUCT_TYPE).setValue(userDetails.getProductType()); mySession.getVariableField(IProjectVariables.GET_CRMCUST_MASTER, IProjectVariables.GET_CRMCUST_MASTER_FIELD_ACCT_NO).setValue(userDetails.getAccountNumber()); mySession.getVariableField(IProjectVariables.GET_CRMCUST_MASTER, IProjectVariables.GET_CRMCUST_MASTER_FIELD_CUSTOMER_CATEGORY).setValue(userDetails.getCustomerCategory()); mySession.getVariableField(IProjectVariables.GET_CRMCUST_MASTER, IProjectVariables.GET_CRMCUST_MASTER_FIELD_NATIONALITY).setValue(userDetails.getNationality()); mySession.getVariableField(IProjectVariables.GET_CRMCUST_MASTER, IProjectVariables.GET_CRMCUST_MASTER_FIELD_PRODUCT_CODE).setValue(userDetails.getProductCode()); mySession.getVariableField(IProjectVariables.GET_CRMCUST_MASTER, IProjectVariables.GET_CRMCUST_MASTER_FIELD_PRODUCT_TYPE).setValue(userDetails.getProductType()); mySession.getVariableField(IProjectVariables.GET_CRMCUST_MASTER, IProjectVariables.GET_CRMCUST_MASTER_FIELD_SERVICE_STATUS).setValue(userDetails.getServiceStatus()); mySession.getVariableField(IProjectVariables.GET_CRMCUST_MASTER, IProjectVariables.GET_CRMCUST_MASTER_FIELD_CURR_PLAN).setValue(userDetails.getCurrPlan()); detailsBean.setAuthenticated_Type(userDetails.getProductType()); detailsBean.setAuthenticated_number(userDetails.getAccountNumber()); mySession.getVariableField(IProjectVariables.FLAGS, IProjectVariables.FLAGS_FIELD_HOSTSERVICE_STATUS).setValue(EtisalatConstants.YES); detailsBean.setCaller_authenticated(EtisalatConstants.REPORT_YES); //Setting the customer segment for reporting purpose detailsBean.setCustomer_segment(userDetails.getCustomerSegment()); //Setting the available products for the caller //detailsBean.setProduct_category(userDetails.getProductType()); detailsBean.setProduct_category(userDetails.getProductCode()); detailsBean.setAccount_number(userDetails.getAccountNumber()); detailsBean.setAuthenticated_number(userDetails.getAccountNumber()); detailsBean.setAuthenticated_Type("AUTO"); }else if(hostErrResponse.equalsIgnoreCase(EtisalatDbConstants.DB_NO_REC_MSG) || hostErrCode.equalsIgnoreCase(EtisalatDbConstants.DB_NO_REC_CODE)){ mySession.getVariableField(IProjectVariables.FLAGS, IProjectVariables.FLAGS_FIELD_HOSTSERVICE_STATUS).setValue(EtisalatConstants.NO); trace.writeln(ITraceInfo.TRACE_LEVEL_ERROR, "No Record Found for the account_number of '"+userDetails.getPhoneNumber()+"'"); }else{ throw new Exception(hostErrResponse); //Line No: 185 } }catch (Exception e) { // TODO Auto-generated catch block detailsBean.setCall_end_reason(EtisalatConstants.CALLEND_REASON_DBDOWN); mySession.getVariableField(IProjectVariables.FLAGS, IProjectVariables.FLAGS_FIELD_HOSTSERVICE_STATUS).setValue(EtisalatConstants.NO); trace.writeln(ITraceInfo.TRACE_LEVEL_ERROR, "Exception : There is an issue while getting customer details "+e); e.printStackTrace(); }finally{ mySession.setProperty(EtisalatConstants.USER_DETAILS, userDetails); mySession.setProperty(EtisalatConstants.CALLER_DETAILS_BEAN_OBJECT, detailsBean); //Host Reporting End Details etisalatMethods.HostEndDetails(mySession, LoggingConstants.NOT_APPLICABLE, LoggingConstants.NOT_APPLICABLE, LoggingConstants.NOT_APPLICABLE, hostErrResponse, LoggingConstants.NOT_APPLICABLE); response = null; etisalatMethods=null; hostErrResponse = null; detailsBean = null; trace = null; userDetails = null; dao = null; } super.requestBegin(mySession); }
Java 2 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; }
The Java code 1 method calls the Java Code 2 method. In java Code 1, I process data received from the oracle. In java code 2, I call a stored procedure.
I handled the sql exception at the procedure level. If there is any error in the procedure, the procedure returns an oracle error code and an error message as a parameter. In java Code 2, I get oracle (1001) error code and save it in hashmap, after that I check if error code is 0 (success). Thus, he does not print the reocrd log. In java Code 1, I get hashmap. From the hash map, I infer the error code value. if the error code is not 0 or -1, I throw an exception that I received from the oracle (printed in the trace log and stack trace log).
Trace log
12/06/2013 06:26:35:063 INFO - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : Start Time of GET_CUSTOMER_DETAILS=1371003995063 12/06/2013 06:26:35:065 INFO - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : Data source connection established 12/06/2013 06:26:35:065 DEBUG - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : Time out=2 12/06/2013 06:26:35:067 DEBUG - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : No records found 12/06/2013 06:26:35:067 INFO - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : end Time of GET_CUSTOMER_DETAILS=1371003995067 12/06/2013 06:26:35:067 INFO - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : Total Time of GET_CUSTOMER_DETAILS=4ms 12/06/2013 06:26:35:067 DEBUG - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : DB Success: 12/06/2013 06:26:35:067 DEBUG - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : ERROR_CODE:-1001 12/06/2013 06:26:35:067 DEBUG - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : ERROR_Response:ORA-01001: invalid cursor 12/06/2013 06:26:35:067 ERROR - EC2E4987556A7FF7DA6DEE8EEE9843FC.jvm2:/ETA_MAIN : Exception : There is an issue while getting customer details java.lang.Exception: ORA-01001: invalid cursor
Stack Trace Log:
java.lang.Exception: ORA-01001: invalid cursor at flow.DB_GetCRM_CustMaster.requestBegin(DB_GetCRM_CustMaster.java:185) at com.avaya.sce.runtime.AppServlet.processRequest(AppServlet.java:81) at com.avaya.sce.runtime.SCEServlet.requestHandler(SCEServlet.java:282) at com.avaya.sce.runtime.SCEServlet.doPost(SCEServlet.java:189) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:630) at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436) at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374) at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302) at com.avaya.sce.runtime.SCEServlet.forward(SCEServlet.java:1303) at com.avaya.sce.runtime.Data.evaluateActions(Data.java:211) at flow.DB_GetCallerProfileDetails.executeDataActions(DB_GetCallerProfileDetails.java:94)
I was not able to reproduce this problem in SQLPlus , it comes only from jdbc calls from application server 2 and application server 3, and repeating the same call usually works (but sometimes it is not executed a couple of times before it is executed), I don’t used the cursor in the procedure. Please help me in this matter.