Error running Stored Procedure on Oracle 10G
Hi,
We were using Oracle 9i with spring JDBC abstraction in our application. All the stored procedure were running fine. Then our company decide to move on to Oracle 10 G and suddenly all the stored procedre which have output value cease to run. When we run the store procedre the log (also the db) shows that stored procedre executed but when spring tries to get return value it stucks and application hangs ( and almost 300 MB log file in JBoss is generated showing almost calls in loop). Our store procedue is as follows (it is well tested and runs fine)
and the application code is as followsCode:CREATE OR REPLACE PROCEDURE Insert_Resource ( p_short_name IN VARCHAR2, p_description IN VARCHAR2, p_duration IN NUMBER, p_provider_required IN CHAR, p_entity_seq_num IN NUMBER, res_seq OUT NUMBER ) IS no_short_name EXCEPTION ; short_name_too_large EXCEPTION; no_provider_required EXCEPTION; v_provider_required CHAR(1); /****************************************************************************** NAME: insert_resource PURPOSE: To insert resource in powermed.resource_profile table from EMR application REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 28/01/2005 Kashif Javed Rana 1. Created this procedure. ******************************************************************************/ BEGIN IF p_short_name IS NULL THEN RAISE no_short_name; END IF; IF LENGTH(p_short_name) > 10 THEN RAISE short_name_too_large; END IF; IF p_provider_required IS NULL THEN RAISE no_provider_required; END IF; v_provider_required := SUBSTR(p_provider_required,1,1); SELECT PRIMARY_SEQ.NEXTVAL INTO res_seq FROM DUAL; INSERT INTO POWERMED.RESOURCE_PROFILE ( SEQ_NUM, SHORT_NAME, DESCRIPTION, DURATION, PROVIDER_REQUIRED, ENTITY_SEQ_NUM) VALUES (res_seq ,p_short_name, p_description, p_duration, v_provider_required, p_entity_seq_num ); COMMIT; EXCEPTION WHEN NO_SHORT_NAME THEN RAISE_APPLICATION_ERROR (-20091, 'Provider Short Name cannot be null.'); WHEN SHORT_NAME_TOO_LARGE THEN RAISE_APPLICATION_ERROR (-20092, 'Short Name cannot be greater than 10 characters.'); WHEN NO_PROVIDER_REQUIRED THEN RAISE_APPLICATION_ERROR (-20093, 'Provider required cannot be null.'); WHEN OTHERS THEN -- Consider logging the error and then re-raise ROLLBACK; RAISE_APPLICATION_ERROR (-20092, SQLERRM); END Insert_Resource; /
after killing the server (jboss 3.2.3) i saw the log files which is as followsCode:private class InsertResourceProfileProcedure extends StoredProcedure { /* insert_resource(P_SHORT_NAME IN VARCHAR2, P_DESCRIPTION IN VARCHAR2, P_DURATION IN NUMBER,P_PROVIDER_REQUIRED IN CHAR,P_ENTITY_SEQ_NUM IN NUMBER) */ public static final String SQL = "insert_resource"; public InsertResourceProfileProcedure(DataSource ds) { setDataSource(ds); setSql(SQL); // Input parametrs to stored procedure in Sequence declareParameter(new SqlParameter("p_short_name", Types.VARCHAR)); declareParameter(new SqlParameter("p_description", Types.VARCHAR)); declareParameter(new SqlParameter("p_duration", Types.BIGINT)); declareParameter(new SqlParameter("p_provider_required", Types.CHAR)); declareParameter(new SqlParameter("p_entity_seq_num", Types.BIGINT)); // Ouptput Parametr ---- PLEASE NOTE THAT OUTPUT PARAMATERS SHOULD COME AFTER INPUT PARAMETERS declareParameter(new SqlOutParameter("res_seq", Types.BIGINT)); compile(); } public ResourceProfileModel executeProcedure(ResourceProfileModel resourceProfileModel, SignInBusinessModel signInBusinessModel) { Map paramHashMap = new HashMap(); paramHashMap.put("p_short_name", resourceProfileModel.getShortName()); paramHashMap.put("p_description", resourceProfileModel.getDescription() != null ? resourceProfileModel.getDescription() : ""); paramHashMap.put("p_duration", resourceProfileModel.getDuration() != null ? resourceProfileModel.getDuration() : new Long("0")); paramHashMap.put("p_provider_required", resourceProfileModel.getProviderRequired()); paramHashMap.put("p_entity_seq_num", signInBusinessModel.getEntitySequenceNumber()); Map out = super.execute(paramHashMap); if (out.containsKey("res_seq")) { resourceProfileModel.setSeqNum( (Long) out.get("res_seq")); } return resourceProfileModel; } } InsertResourceProfileProcedure insertResourceProfileProcedure = new InsertResourceProfileProcedure(this.getDataSource()); insertResourceProfileProcedure.executeProcedure(resourceProfileModel, signInBusinessModel);
Please help me asap.Code:Calling stored procedure [{call insert_resource(?, ?, ?, ?, ?, ?)}] 2005-01-28 20:07:26,370 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] Opening JDBC connection 2005-01-28 20:07:26,432 INFO [org.springframework.jdbc.oracle.OracleOCIConnectionPoolDataSource] Given connecttion to user: MUGHAL password: mu755096387 2005-01-28 20:07:26,432 DEBUG [org.springframework.jdbc.core.StatementCreatorUtils] Setting SQL statement parameter value; columnIndex=1, parameter value='HNBBN', valueClass=java.lang.String, sqlType=12 2005-01-28 20:07:26,432 DEBUG [org.springframework.jdbc.core.StatementCreatorUtils] Setting SQL statement parameter value; columnIndex=2, parameter value='FGF', valueClass=java.lang.String, sqlType=12 2005-01-28 20:07:26,432 DEBUG [org.springframework.jdbc.core.StatementCreatorUtils] Setting SQL statement parameter value; columnIndex=3, parameter value='5', valueClass=java.lang.Long, sqlType=-5 2005-01-28 20:07:26,432 DEBUG [org.springframework.jdbc.core.StatementCreatorUtils] Setting SQL statement parameter value; columnIndex=4, parameter value='Y', valueClass=java.lang.String, sqlType=1 2005-01-28 20:07:26,432 DEBUG [org.springframework.jdbc.core.StatementCreatorUtils] Setting SQL statement parameter value; columnIndex=5, parameter value='1', valueClass=java.lang.Long, sqlType=-5 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.execute() returned 'false' 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 2005-01-28 20:07:26,463 DEBUG [org.springframework.jdbc.core.JdbcTemplate] CallableStatement.getUpdateCount() returned 1 ............................................ .......................................... .......................(the same statement)
Reply With Quote
Originally Posted by trisberg
