Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: dbms_pipe

Re: dbms_pipe

From: DaLoverhino <>
Date: 7 Apr 2005 15:50:56 -0700
Message-ID: <>

Carlos wrote:
> It seems you are following my advice about using DBMS_PIPE ;-)

Ummm... yes. :) Actually, I've been trying out various ways to extract sql-select statements from the database, and TCP was the last option I was trying out. I then went back to pipes+policy function because that seemed to be the best route in terms of performance and flexibility.

> It would be a help if you could explain more about 'your process'.
> what you really mean with 'dies', 'restarts', 're-attach' and so.

Okay... so basically, I'm trying to capture sql-select statements using policy functions instead of using Oracle's auditing feature. So everytime a select is called against a table, say FOO, Oracle calls my policy function. The policy function writes information to the pipe.

Let's throw out some names: 'SQL_PIPE' is the pipe. READ_SQL is the proc that reads from the pipe, calling the pipe package's functions receive_message and unpack_message. WRITE_SQL is the policy function.

My C/C++ program uses OCI to call READ_SQL, and that's how I extract information from the pipe. Let's call it read_program. Now SELECT_PROC is a test procedure which does 32,000 random selects against table FOO which has 100,000 rows. SELECT_PROC takes about 80 seconds to complete.

So here's the scenarios:

Scenario A (OKAY):
{start read_program, exec SELECT_PROC} x N

Scenario B (OKAY):
{start read_program, exec SELECT_PROC, kill read_program} x N.

Scenario C (OKAY for server, read_program never restarts): start read_program, exec SELECT_PROC, kill read_program, {exec SELECT_PROC} x N.

Scenario D (OKAY for server, read_program hangs on restart): {start read_program, exec SELECT_PROC, kill read_program in the middle of exec'ing, exec SELECT_PROC finishes} x N

Scenario E (OKAY for server, read_program hangs on restart): start read_program, exec SELECT_PROC, kill read_program in the middle of exec'ing, exec SELECT_PROC finishes, start read_program, {exec SELECT_PROC} x N.

So, to kill read_program I simply press Ctrl-C to kill my program. To restart my program, I simply enter the name of the program, read_program, on the command line. What I mean by 'reattach' is simply starting read_program. read_program merely calls READ_SQL.

So here's the summary:

  1. You can pump things through the pipe without a reader on the other end.
  2. Once the reader process goes 'on line' (in my case, once you start the process on the command line), you can then get data out of the pipe and into your outside process.
  3. Once that reader dies, you can still put data in the pipe. The pipe will fill with data, and if you have a time out variable, you can keep calling the function that adds data in your pipe without hanging.
  4. When you restart the reader, writing to the pipe goes on as normal, but reading the pipe runs into problems. It is here where you reader hangs.

Now I thought I could call dbms_pipe.purge( 'pipe name') to correct for D, but that doesn't work. The only thing that seems to work is calling reset_buffer.

Well, thanks for your time. I'm trying to contact Oracle and see if they can help me out aswell. Thanks again!

-Godfrey T. Degamo Received on Thu Apr 07 2005 - 17:50:56 CDT

Original text of this message