Announcement Announcement Module
Collapse
No announcement yet.
Error running Stored Procedure on Oracle 10G Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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)
    Code:
    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;
    /
    and the application code is as follows
    Code:
    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);
    after killing the server (jboss 3.2.3) i saw the log files which is as follows

    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)
    Please help me asap.

  • #2
    That's due to a bug in the Oracle 10g JDBC driver. Upgrade to a later version - there is a least one patch release available. If you happen to have WebLogic installed, then you can use their Oracle JDBC driver - it is a later compile and includes the fix for this problen, even though it shows the same version number as the original Oracle 10g one.

    Comment


    • #3
      Hello,
      Thanks Trisberg for your immediate reply but unfortunately it did not worked for me.As told by you i downloaded latest Oracle 10G drivers from Oracle site but they did not worked and same problem arised again. We are using Oracle 10.1.0.2.0 and jboss 3.2.3 (not weblogic) and now the latest oracle drivers too. Also we are using OCi driver not thin driver( we have some specific issues). Please help me asap . Thanks in advance.

      Comment


      • #4
        You need to use the 10.1.0.3 driver that is part of the 10.1.0.3 (Server Patch Set). This driver is AFAIK not available as a separate download.

        You could also use the 10.1.0.2 driver that comes with BEA WebLogic 8.1SP3 or the 9.2.0.3 driver.

        If you have an Oracle Metalink account, then you can look up "Bug 3563038 - Statement.getUpdateCount() returns 1 instead of -1" for more info.

        Comment


        • #5
          Hello,
          Thanks a lot Trisberg it solved my problem. I would also like to thanks the whole Spring community for there excellent framework and very good support. Long Live Spring......

          Comment


          • #6
            Originally posted by trisberg View Post
            That's due to a bug in the Oracle 10g JDBC driver. Upgrade to a later version - there is a least one patch release available. If you happen to have WebLogic installed, then you can use their Oracle JDBC driver - it is a later compile and includes the fix for this problen, even though it shows the same version number as the original Oracle 10g one.
            I know this thread is ancient but anyway cheers for that...I updated Oracle JDBC driver and error is now gone..

            Comment

            Working...
            X