Tailing work in the db as it run with pipes

  Doesn't seem that long ago, I needed to monitor a process as it ran.  So, I wrote a small wrapper over dbms_pipe to look like dbms_output so it was easy to switch back and forth.  That was in 2004 so the file's timestamp says.  Time flies !

  DBMS_PIPE is nothing new and many have blogged it's good and bad.  There's lots more but here's just a few:

http://thinkoracle.blogspot.com/2005/11/dbmspipe.html

http://www.hoekstra.co.uk/index.php/software-mainmenu-36/oracle-mainmenu-54/29-dbmspipe.html

http://www.jaredstill.com/content/debug-pipe.html

  Here's the wrapper I made and the body is here:

CREATE OR REPLACE PACKAGE pipe_output IS
   pv_pipe_on_bln BOOLEAN := false;
   PROCEDURE set_pipeoutput_on;

   PROCEDURE put_line (p_message_txt VARCHAR2);
   PROCEDURE put_line (p_pipe_name VARCHAR2,p_message_txt VARCHAR2);
   
   PROCEDURE get_line (p_waittime_num NUMBER := 1);
   PROCEDURE get_line (p_pipe_name VARCHAR2,p_waittime_num NUMBER := 1);
   PROCEDURE get_line (p_pipe_name IN VARCHAR2,p_waittime_num IN NUMBER := 1,p_message OUT VARCHAR2);
END pipe_output;

So what that would allow me to do in the plsql block was something like this.

begin
   pipe_output.set_pipeoutput_on;
  for r in ( select object_name from user_objects ) loop
    pipe_output.put_line('TAIL_ME',r.object_name);
  end loop;
end;

Now that something is in the pipe it's time to get it out. For that I wrote a small java program.  The same thing can be done direct from a sqlplus script or perl or whatever, it's not complicated.

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class TailDBMSPipe extends Thread {
    private String v_pipe_name;
    private String connName;

    public TailDBMSPipe(String pipeName) {
        super.setDaemon(true);
        v_pipe_name = pipeName;
    }

    public static void main(String[] args) {
        TailDBMSPipe p = new TailDBMSPipe(args[0]);
        p.connName = "jdbc:oracle:thin:" + args[1];
        //p.connName = "jdbc:oracle:thin:my_customers/oracle@ap6002wgs.us.oracle.com:1521:mcdev";
        p.run();
    }

    public void run() {
        System.out.println("Redirecting database pipe:" + v_pipe_name);
        Connection conn = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(connName);

            String s = null;
            BigDecimal retVal;
            BigDecimal bigZero = new BigDecimal(0);

            while (true) {
                CallableStatement cs = conn.prepareCall("{ call ? := DBMS_PIPE.RECEIVE_MESSAGE(?, ?) }");
                cs.registerOutParameter(1, Types.INTEGER);
                cs.setString(2, v_pipe_name);
                cs.setString(3, "100");
                cs.executeUpdate();
                retVal = cs.getBigDecimal(1);
                cs.close();

                if (retVal.compareTo(bigZero) == 0) {
                    cs = conn.prepareCall("{ call DBMS_PIPE.UNPACK_MESSAGE( ?) }");
                    cs.registerOutParameter(1, Types.VARCHAR);
                    cs.executeUpdate();
                    s = cs.getString(1);
                    cs.close();

                    if (s != null) {
                        System.out.println(v_pipe_name + ":" + s);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.close();
            } catch (Exception ex) {
                System.out.println("could not close");
            }
        }
    }
}

The results is that you can watch what comes across the pipe very fast.  You may notice the timestamps in the output of what's in the demo here: