Re: buffer overflow, limit of 2000 bytes (how to get around)

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 26 Nov 2002 14:23:37 -0800
Message-ID: <336da121.0211261423.2129c6cc@posting.google.com>

"Scott Mattes" <Scott_at_TheMattesFamily.ws> wrote in message news:<gFLE9.6498$kO5.1886547_at_news1.news.adelphia.net>...

> I would be interested in the JAVA version. When I was trying different
> things to find a adequate real time msgr I didn't seem to be able to do the
> pipe idea correctly (msgs didn't always arrive, if I remember correctly).
> 

OK. Java version is the only one I currently have. Some history. I've got the idea from Sergei Kuchin (http://otl.sourceforge.net/) in 1996. He had it written in Pro*C at the time. Later (2000) I wrote java version. Never had problems with messages, all messages always arrived.

You need 3 pieces of software.

1. PL/SQL procedure to simplify message sending.

procedure send_msg(m in varchar2) is
  ms number;
begin

    dbms_pipe.pack_message(m);
    ms := dbms_pipe.send_message('JavaPipe');     fnd_file.put_line(fnd_file.log, m);
end send_msg;

2. Java class implementing the listener.

// This is a listener class which connects to Oracle instance and
// listens for requests. It uses Oracle package dbms_pipe.
// This package allows you to send messages between different sessions.
// It is implemented as a thread, so you can start several listeners for
// several databases. You also can periodically check if connection is
// alive by checking piperet variable. If connection appears to be dead,
// you can kill and restart thread. The messages in pipe would stay there for
// a long time, so if listener didn't listen for a while, it'd pick up all
// messages from pipe after restart.

// You need to import the java.sql package to use JDBC import java.sql.*;

public class PipeThread extends Thread
{

    String ConnectS;
    String usrn;
    String pwd;
    int piperet;
    public PipeThread(String ConnectString, String username, String passwd)     {

	// Constructor. ConnectString parameter has to have format:
	// jdbc:oracle:<drivertype>:@<host_name/host_address>:<port>:<instance>
	// Example:
	// jdbc:oracle:thin:@alex:1521:oracle

	super();
	piperet = 0;
	ConnectS = ConnectString;
	usrn = username; // Oracle username
	pwd = passwd;    // Oracle password

    }

    public void run() {

	try {


	    // Load the Oracle JDBC driver

	    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());



	    // Connect to the database

	    Connection conn =
		DriverManager.getConnection (ConnectS, usrn, pwd);



	    // Create a Statement

	    Statement stmt = conn.createStatement ();

	    int i = 0;
	    int n;
	    String zip;
	    String Address;

	    String GetZip = "begin dbms_pipe.unpack_message(?); end;";



	    // Prepare statement listening the pipe.

	    // Get a message from pipe, set delay to 60 sec.

	    String GetFromPipe = 
		"begin ? := dbms_pipe.receive_message('JavaPipe',60); end;";
	    CallableStatement PipeListen = conn.prepareCall(GetFromPipe);
	    PipeListen.registerOutParameter(1,Types.INTEGER);
	    CallableStatement PipeGetZip = conn.prepareCall(GetZip);
	    PipeGetZip.registerOutParameter(1,Types.CHAR);



	    // Main loop

	    while (true){
		PipeListen.execute();

		piperet = PipeListen.getInt(1);

		// Return codes from receive_message
		// 0: message received
		// 1: timeout
		// >1: Oracle error, shoudn't happen

		// If message received:
		if (piperet == 0) {
                  PipeGetZip.execute();
                  zip = PipeGetZip.getString(1);
                  zip = zip.trim();

                  System.out.println(zip);
		}

		if (piperet > 1) System.out.println("Oops!");
	    }
	} catch (SQLException e){


	    // Something wrong. Use your own printout/logging.

	    // I'd prefer to end thread here and restart it from

	    // calling class.

	    // I'd also recommend to check class variable piperet

	    // once in a while

	    // and kill and restart thread if it > 1.

	    System.out.println(e);
	}

    }
}

3. Example of java program running the listener.

public class PipeTest {

  int myCounter = 0;
  static int classCounter = 0;   

  public static void main (String[] args) {     PipeThread t1 = new
PipeThread("jdbc:oracle:thin:@machine_name:1521:instance_name",

					 "username", "password");
     t1.start();

  }

}

It's possible to run several listeners at the same time. I don't think that reading output would be easy though. All restrictions of dbms_pipe package should be taken into account. And, of course, you need jdbc thin driver.

> 
> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0211251533.6b103a6c_at_posting.google.com...

<some snip>

> >
> > There is another method of PL/SQL debugging which let's you to receive
> > debugging messages real time. You just send your messages through
> > dbms_pipe package. You need a listener which would receive this message
> > and print it. Usually it's a small Pro*C or java program. I can post
> > a sample if anyone is interested.
> >
> > Alex.

Received on Tue Nov 26 2002 - 16:23:37 CST