Re: logging information from a stored procedure calls

From: Chet Justice <chet.justice_at_pfsf.org>
Date: 12 May 2005 08:49:00 -0700
Message-ID: <1115912940.022733.26410@g44g2000cwa.googlegroups.com>

booksnore wrote:
> Hi,
> I'm involved in performance analysis for an application that calls a
> stored procedure that contains if logic. I need to log how many times
> logic conditions within the procedure are met so I need to write
> information to a log. So it would be something like this...
>
> CREATE OR REPLACE PROCEDURE test1 (
> myValue in char)
> is
> begin
>
> if myValue is null then
> -- write information to log saying stored procedure test1
> -- condition 1
> else
> -- write information to log saying stored procedure test1
> -- condition 2
>
> end if;
>
> END test1;
> /
>
> What's the easiest way to implement this in PL/SQL? I have no access
to
> any monitoring tools. The procedure is called from a web application,
> my only means of logging would be to a file. Any help appreciated.

Why not just log it to a table?

CREATE TABLE logging_tab
(
  loggingid NUMBER PRIMARY KEY,

  func_owner VARCHAR2(30),
  func_name VARCHAR2(30),
  date_created DATE DEFAULT SYSDATE,

  whatever_you_want_to_log VARCHAR2(100) --<--add as many as you need );

CREATE SEQUENCE sq_loggingid
  START WITH 1
  INCREMENT BY 1; CREATE OR REPLACE
PROCEDURE logging_proc

  (p_funcowner IN VARCHAR2,
   p_funcname IN VARCHAR2,
   p_whateveryouwanttolog IN VARCHAR2)

IS
BEGIN
  INSERT INTO logging_tab(loggingid, func_owner, func_name, whatever_you_want_to_log)
  VALUES (sq_loggingid.nextval, p_funcowner, p_funcname, p_whateveryouwanttolog);
END logging_proc;
/
show errors

Then just stick that procedure everywhere you want to log. Then just use views on the table to create log reports.

chet Received on Thu May 12 2005 - 10:49:00 CDT