Announcement Announcement Module
Collapse
No announcement yet.
Stored Proc Handled Exceptions Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored Proc Handled Exceptions

    I just need a pointer on how to handle exceptions that are caught within
    a stored procedure itself.

    I'm using oracle stored procedures combined with springs storedprocedure class.

    If an exception occurs such as database down then Spring will wrap this in
    its DataAccessException hierarchy, which is the only exception my Service layer throws.

    My question relates to exceptions that are are handled inside the Stored Proc itself.

    For example we have something like :


    PROCEDURE select_something (error_code OUT NUMBER,
    some_in_param VARCHAR2,
    results OUT T_CURSORTYPE) IS


    BEGIN


    //do some stuff if worked return error_code 0
    error_code := 0;



    EXCEPTION
    WHEN SPECIFIC_EXCEPTION THEN
    error_code := -1;
    WHEN OTHERS THEN error_code := -2;


    END select_something;



    Where we will get a -n error_code if an exception has occurred. Now Spring isn't going to know about these is it? They're caught locally within the stored proc itself?

    I therefore need to check whether the error_code is 0.
    If it's -n I was thinking of subclassing DataAccessException with my own specific exception. The client can then deal with one exception, if it chooses to, and can always get the specific exception if it needs to.

    Does this seem right or have I missed the point as to how Spring handles data access exceptions?

  • #2
    You can create your own DataAccess exceptions and throw them when you detect an error condition being returned from the stored procedure.

    An alternative to the error code is having the stored procedure raise its own application specific exception like this:

    Code:
            PROCEDURE raise_price (p_id NUMBER, p_new_price DECIMAL) AS
              m_curr_price DECIMAL;
            BEGIN
              SELECT price INTO m_curr_price FROM beers WHERE id = p_id;
              IF m_curr_price > p_new_price THEN
               raise_application_error(-20101, 'New price is less than current price');
              ELSE
                UPDATE beers SET price = p_new_price WHERE id = p_id;
              END IF;
            END;
    and then add in a custom translation entry in your own copy of sql-error-codes.xml like:

    Code:
        <bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes">
            <property name="badSqlGrammarCodes">
                <value>900,903,904,917,923,925,936,942,17006</value>
            </property>
            <property name="invalidResultSetAccessCodes">
                <value>17003</value>
            </property>
            <property name="dataAccessResourceFailureCodes">
                <value>17002</value>
            </property>
            <property name="dataIntegrityViolationCodes">
                <value>1,1400,1722,2291</value>
            </property>
            <property name="cannotAcquireLockCodes">
                <value>54</value>
            </property>
            <property name="customTranslations">
                <list>
                  <bean
                    class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
                    <property name="errorCodes">
                      <value>20101</value>
                    </property>
                    <property name="exceptionClass">
                      <value>com.springdeveloper.BeerPriceException</value>
                    </property>
                  </bean>
                </list>
            </property>
        </bean>
    That would be my preferred way of handling this.

    Comment


    • #3
      Hi,

      this is exactly how i plan to deal with errors from oracle. What i'm not sure about is am i expected to edit the sql errors xml file INSIDE the spring.jar file? Or create a copy and put that in the class path and edit that???

      Thanks

      Rakesh

      Comment


      • #4
        Create a copy and put that in the class path and edit that.

        Comment


        • #5
          can i remove the other database beans? Or is it not worth the trouble?

          Rakesh

          Comment


          • #6
            I would if I'm only using one database.

            Comment


            • #7
              taking this further:

              At the moment, i can get 2 different types of authentication errors with Oracle:

              1. Bad username/password - ORA-01017
              2. Account locked - ORA-28000

              Right now they both get wrapped as CannotGetJdbcConnection. If i want to distinguish between them (so i can give a better error message to the user) I have 2 choices:

              1. Create a specific Exception class for each one and (somehow) configure it to be thrown automatically.
              2. Parse the error message looking for the ORA code.

              (2) is easy i guess but not the most elegant.

              I can do what you did in your example above but that assumes a new hierarchy of exceptions whereas these are really sub types of CannotGetJdbcConnection.

              What do you recommend? Create new 2 new exceptions classes and map them standalone or put them into the correct place in the hierarchy(how?).

              Thanks

              Rakesh

              Comment


              • #8
                Here's my attempt but its not working - I only get CannotGetJdbcConnectionException:

                my sql-error-codes.xml file:

                Code:
                <bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes">
                		<property name="badSqlGrammarCodes">
                			<value>900,903,904,917,936,942,17006</value>
                		</property>
                		<property name="invalidResultSetAccessCodes">
                			<value>17003</value>
                		</property>
                		<property name="dataAccessResourceFailureCodes">
                			<value>17002</value>
                		</property>
                		<property name="dataIntegrityViolationCodes">
                			<value>1,1400,1722,2291</value>
                		</property>
                		<property name="cannotAcquireLockCodes">
                			<value>54</value>
                		</property>
                		<property name="customTranslations">
                            <list>
                              <bean
                                class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
                                <property name="errorCodes">
                                  <value>1017</value>
                                </property>
                                <property name="exceptionClass">
                                  <value>com.amex.ifst.data.exceptions.OracleUsernamePasswordException</value>
                                </property>
                              </bean>
                              <bean
                                class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
                                <property name="errorCodes">
                                  <value>28000</value>
                                </property>
                                <property name="exceptionClass">
                                  <value>com.amex.ifst.data.exceptions.OracleAccountLockedException</value>
                                </property>
                              </bean>
                            </list>
                        </property>
                	</bean>

                here's how i have written one of the exceptions:

                Code:
                public class OracleUsernamePasswordException extends
                		CannotGetJdbcConnectionException {
                
                	public OracleUsernamePasswordException(String msg, SQLException ex) {
                		super(msg, ex);
                	}
                	
                	public OracleUsernamePasswordException(String msg, ClassNotFoundException ex) {
                		super(msg, ex);
                	}
                }
                and here's my code where i try and catch the error (i am using Acegi and mapping my custom exceptions to Acegi's):

                Code:
                try {
                            List dbAuths = getRolesSP.run(authentication);
                
                            //addCustomAuthorities(user.getUsername(), dbAuths);
                            arrayAuths = (GrantedAuthority[]) dbAuths.toArray(arrayAuths);
                        } catch (OracleUsernamePasswordException e) {
                            _logger.warn("Logon FAILURE for username:{}", username);
                            throw new BadCredentialsException("username/password incorrect.");
                        }
                        catch (OracleAccountLockedException e) {
                        	_logger.warn("Logon FAILURE for username:{}", username);
                            throw new LockedException("account LOCKED.");
                        }
                What am i doing wrong?

                Thanks

                Rakesh

                Comment


                • #9
                  can no one help???

                  Comment


                  • #10
                    I've come to the conclusion that the workflow for error handling is different for certain types of sql errors.

                    I followed the code hoping to find where the oracle code was examined in order for Spring to throw CannotGetJdbcConnection.

                    Turns out that the exception is created whenever a getConnection fails. It doesn't deal with oracle sql error mapping codes at all.

                    I decided to go with the parsing of the vendor-specific error code:

                    Code:
                    catch (CannotGetJdbcConnectionException e) {
                                // could not establish connection so something wrong with credentials
                                if (e.getMessage().indexOf("ORA-01017") > -1) {
                                    _logger.warn("Logon FAILURE for username:{}", username);
                                    throw new BadCredentialsException(
                                        "username/password incorrect.");
                                } else if (e.getMessage().indexOf("ORA-28000") > -1) {
                                    _logger.warn("Logon FAILURE for username:{}", username);
                                    throw new LockedException("account LOCKED.");
                                }
                    Let me know if i have it wrong.

                    Rakesh

                    Comment

                    Working...
                    X