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

Thank you.

What is the 'fnd_file.put_line' all about? I don't recognize that as standard PL/SQL.

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0211261423.2129c6cc_at_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:35:09 CST