Print DBMS_OUTPUT.put_line with Hibernate or Spring

Question

I would like to know if Hibernate or Spring or any 3rd party lib provide the ability of printing DBMS_OUTPUT.put_line messages directly to system.out or a file of log.

The purpose is to have both PLSQL log messages and java log messages in the console.

I know there is a similar question of which the answer is to turn PLSQL procedure to function in returning the log message, but this doesn't suite my situation. In fact my log message is too complex that returning it in a function is impossible.

I have read this https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45027262935845 and got inspiration but I'm wondering if there is an out of box solution to avoid whales reproducing.

Answer 1

Since nobody answerred this question, I suppose there isn't any out-of-box solution. So I built it myself and post my codes here for whom it may interests.

Anyone who want to improve this solution is welcome! Just don't be shame to share yours!

Solution based on Spring AOP. An annotation @DbmsOutput is created for adding DBMS_OUTPUT aware ability on relating methods.

Env : Maven + Spring 4.2.6 + Hibernate 5.1.0

Step 1: adding Spring AOP dependencies

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aop</artifactId>
    <version>4.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjrt</artifactId>
    <version>1.8.4</version>
</dependency>
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.8.4</version>
</dependency>

Step 2: creating @DbmsOutput

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public @interface DbmsOutput {
}

Step 3: creating interceptor for all methods declared with @DbmsOutput.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.log4j.Logger;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.Work;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;

@Aspect
@Order(Ordered.LOWEST_PRECEDENCE)
public class DbmsOutputAspect {

    private static final Logger LOGGER = Logger.getLogger(DbmsOutputAspect.class);

    @Autowired
    protected SessionFactory sessionFactory;
    /**
     * make Dbms output aware enable/disable configurable in spring bean declaration
     */
    private boolean enable = true;
    private int size = 1000000;

    public boolean isEnable() {
        return enable;
    }

    public void setEnable(final boolean enable) {
        this.enable = enable;
    }

    public int getSize() {
        return size;
    }

    public void setSize(final int size) {
        this.size = size;
    }

    @Pointcut("execution(@DbmsOutput * *(..))")
    public void DbmsOutputInterceptMethod() {
    }

    @Around("DbmsOutputInterceptMethod()")
    public Object around(final ProceedingJoinPoint point)
            throws Throwable {

        if (isEnable()) {
            LOGGER.debug("before DBMS_OUTPUT point cut");
            Query queryEnable = sessionFactory.getCurrentSession().createSQLQuery("call dbms_output.enable(:size)");
            queryEnable.setParameter("size", getSize());
            queryEnable.executeUpdate();
        }
        try {
            return point.proceed(point.getArgs());
        } finally {
            if (isEnable()) {
                sessionFactory.getCurrentSession().doWork(new Work() {

                    @Override
                    public void execute(final Connection connection)
                            throws SQLException {

                        CallableStatement show_stmt = connection.prepareCall(
                                "declare " +
                                        " l_line varchar2(255); " +
                                        " l_done number; " +
                                        " l_buffer long; " +
                                        "begin " +
                                        " loop " +
                                        " exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " +
                                        " dbms_output.get_line( l_line, l_done ); " +
                                        " l_buffer := l_buffer || l_line || chr(10); " +
                                        " end loop; " +
                                        " :done := l_done; " +
                                        " :buffer := l_buffer; " +
                                        "end;");
                        int done = 0;

                        show_stmt.registerOutParameter(2, java.sql.Types.INTEGER);
                        show_stmt.registerOutParameter(3, java.sql.Types.VARCHAR);

                        for (;;) {
                            show_stmt.setInt(1, 32000);
                            show_stmt.executeUpdate();
                            LOGGER.info(show_stmt.getString(3));
                            done = show_stmt.getInt(2);
                            if (done == 1) {
                                break;
                            }
                        }
                    }
                });
                Query queryDisable = sessionFactory.getCurrentSession().createSQLQuery("call dbms_output.disable()");
                queryDisable.executeUpdate();
                LOGGER.debug("after DBMS_OUTPUT point cut");
            }
        }
    }
}

Step 4: Configuring DbmsOutputAspect and TransactionManager in Spring

<!-- aspectj -->
<aop:aspectj-autoproxy proxy-target-class="true" />
<bean class="DbmsOutputAspect" />

<!-- transaction -->
<tx:annotation-driven transaction-manager="transactionManager" order="0" />
<bean id="transactionManager" class="org.springframework.orm.hibernate5.HibernateTransactionManager">
    <property name="sessionFactory">
        <ref bean="sessionFactory" />
    </property>
</bean>