Wed, 23 Sep 1998 13:58:45 GMT, kjohnson29_at_my-dejanews.com rambled:
>I'm interested in creating a trigger that will write columns from the
>triggered row to a named pipe. The process on the other side of the pipe is
>a non-PL/SQL program, likely written in C or PERL. I had intended to use the
>UTL_FILE package to do this, if possible, but now I'm wondering about the
>DBMS_PIPE package. Does anyone have any info on doing this? Any example
>code? Any reasons to use one method over the other?
DBMS_PIPE is a bit more flexible than UTL_FILE, as it doesn't require
new init.ora parameters or anything.
Here is a package which can be used to write debug information to a
pipe:
CREATE OR REPLACE PACKAGE trace AS
- This package contains a group of procedures which write debug
- messages to a named pipe, with timestamps and functional
indenting.
- Calls to these procedures can be left in your code, and trace
turned
- on and off when necessary. Writing the messages to a pipe means
you
- can debug code on client and server simultaneously and get all
debug
- output in one session.
- This requires a program of some kind running in another session
to
- read from the named pipe and output to the screen/logfile. I
use the
- C example shown in the Application Developers Guide.
- During normal running of a system, trace would be off, even
though all
- the trace statements are still in the code. Trace_on turns in
trace for
- the current session.
- b_pipe is the name of your pipe.
- b_level can be a value from 0 to 9
- 0 is all messages
- 9 is just function entry statements
PROCEDURE trace_on (b_pipe varchar2, b_level integer default
0);
- This turns off trace after a call to trace_on
PROCEDURE trace_off;
- Put this at the start of a procedure/function. It puts an
'Entering...'
- message onto the pipe, and increases the indent level
PROCEDURE trace_entry (b_name varchar2);
- Put this at the end of a procedure/function. It puts an
'Exiting...'
- message onto the pipe and decreases the indent level
PROCEDURE trace_exit (b_name varchar2);
- Sprinkle these throughout your code, with optional levels, for
- whatever debug messages you require
PROCEDURE trace_print (b_string varchar2, b_level integer default
0);
PRAGMA RESTRICT_REFERENCES (trace_on,WNDS,RNDS);
PRAGMA RESTRICT_REFERENCES (trace_off,WNDS,RNDS);
PRAGMA RESTRICT_REFERENCES (trace_entry,WNDS);
PRAGMA RESTRICT_REFERENCES (trace_exit,WNDS);
PRAGMA RESTRICT_REFERENCES (trace_print,WNDS);
END trace;
/
CREATE OR REPLACE PACKAGE BODY trace AS
- number of spaces to use for each indent on trace output
cv_indent_length constant integer := 4;
- var to hold current pipe name
gv_debug_pipe varchar2(16);
- var to hold trace level
gv_debug_level integer;
- var to hold current indent level
gv_indent integer := 0;
- general return variable
gv_return integer;
PROCEDURE trace_on (b_pipe varchar2,
b_level integer default 0) IS
BEGIN
- set global pipe name and trace level for other functions to
use
gv_debug_pipe := b_pipe;
gv_debug_level := b_level;
END trace_on;
PROCEDURE trace_off IS
BEGIN
- get rid of current pipe name
gv_debug_pipe := NULL;
gv_debug_level := 0;
END trace_off;
PROCEDURE trace_entry (b_name varchar2) IS
BEGIN
- if a pipe is defined, write a function entry message
- to it, and increase the indent level
if gv_debug_pipe is not null then
dbms_pipe.pack_message(lpad('
',gv_indent*cv_indent_length)||
to_char(sysdate,'HH:MI:SS')||' Entering
'||b_name);
gv_return := dbms_pipe.send_message(gv_debug_pipe);
gv_indent := gv_indent + 1;
end if;
END trace_entry;
PROCEDURE trace_exit (b_name varchar2) IS
BEGIN
- reduce indent
if gv_indent > 0 then
gv_indent := gv_indent - 1;
end if;
- if a pipe is defined, and trace level is correct write
- a function exit message to it and decrease the indent level
if gv_debug_pipe is not null and gv_debug_level < 9 then
dbms_pipe.pack_message(
lpad(' ',gv_indent*cv_indent_length)||
to_char(sysdate,'HH:MI:SS')||' Exiting '||b_name);
gv_return := dbms_pipe.send_message(gv_debug_pipe);
end if;
END trace_exit;
PROCEDURE trace_print (b_string varchar2, b_level integer default 0)
IS
BEGIN
- if a pipe is defined, write a general message to it
if gv_debug_pipe is not null and b_level >= gv_debug_level then
dbms_pipe.pack_message(
lpad(' ',gv_indent*cv_indent_length)||
to_char(sysdate,'HH:MI:SS')||' '||b_string);
gv_return := dbms_pipe.send_message(gv_debug_pipe);
end if;
END trace_print;
END trace;
/
Mark Styles
Spam my account, lose your account. Clear enough?
Religious tolerance is an oxymoron.
Received on Wed Sep 23 1998 - 09:19:54 CDT
