Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: dbms_pipe
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'.
And
> 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:
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