Returning meaningful error from Oracle Policy

Question

I have an Oracle policy and at one point I want to stop processing and return a meaningful error to Hibernate backed by Oracle JDBC.

In my policy I do the following when a certain error is seen:

RAISE_APPLICATION_ERROR( -20001, 'Unauthorized' );

Note the message can be anything, this is just an example.

And that works just fine and the policy stops execution, along with the SQL call and returns to the calling function (IE Hibernate).

In the JDBC driver though my code and error are expunged from the exception information:

]] Root cause of ServletException. org.hibernate.exception.GenericJDBCException: could not extract ResultSet at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) Truncated. see log file for complete stacktrace Caused By: java.sql.SQLException: ORA-28112: failed to execute policy function

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) Truncated. see log file for complete stacktrace

Deep diving into the JDBC driver my -20001 error and the word "Unauthorized" are no longer to be seen. Is there any way of returning a meaningful error upwards to the calling programme? Or does, what seems to be the "policy marshaller", take all exceptions and re-wraps them into another error?

Is this the best way of returning a proper failure to the calling programme or is there a better method?

I don't want to return no results, as that means everything is OK, I want to return a proper error.

UPDATE: Using the exception trapper from @a1ex07 I see that the error is really gone and all there is a "process policy" error in it's place. The exact stack trace is as follows:

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:144) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)

At this point:

oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)

There is still no sign of my -20001 or the text. It would appear the driver completely removes my exception and replaces it with it's own bland one.

UPDATE: The calling Hibernate code is as follows:

public List<Tag> findBy(Long termId, String tagName) {
    try {
       Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Tag.class)
            .createAlias("tagTerms", "tagTerms");
        if (tagName != null) {
          criteria.add(Restrictions.eq("tagName", tagName));
       }
       //termId not used yet        
       criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
       return criteria.list();
    } catch (RuntimeException re) {
        //SQL error is from the oracle policy and not anything else.
        log.error("get failed ", re);
        throw re;
    }
}

A Tag is a standard Hibernate Entity.

I've currently reverted back to using empty result set at least that does not cause general SQL errors. I get the feeling this isn't possible, as the error just isn't transmitted back to the calling procedure.

Hope the community can help here.

a1ex07 a1ex07 · Accepted Answer · 2016-10-05T17:30:26

The problem as I see it is not with Oracle, but how to retrieve error code from the exception thrown by Hibernate. exception.getCause() or exception.getCause().getCause() should have error code from RAISE_APPLICATION_ERROR . For instance, your exception handler (Java example) may look like

public void handleSQLException(Exception e) throws ECustomException
    {
        SQLException se = null;

        if (null != e.getCause() )
        {
            if (e.getCause() instanceof SQLException )
            {
                se = (SQLException)e.getCause();
            }
            if (null != e.getCause().getCause() && e.getCause().getCause() instanceof SQLException )
            {
                se = (SQLException)e.getCause().getCause();
            }
        }
        if (null != se)
        {

            int eCode = se.getErrorCode();
            if (eCode >0)
            {
                eCode = -eCode;
            }
            switch (eCode)
            {
                case -20001 : 
                    throw new EInvalidPolicy(se);
                --handling other defined other error codes 
                default :
                throw new RuntimeException(e);
            }
        }
        else
        {
            throw  new RuntimeException(e);
        }
    }

Returning meaningful error from Oracle Policy

1 Answers