Rem drv: <create type="pkgbodys" pos="template/template_pkgbody.sql+"/>
Rem
Rem $Header: trace_log_pkgbody.sql 03-aug-2006.22:59:14 sthiruna Exp $
Rem
Rem trace_log_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      trace_log_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sthiruna    07/31/06 - Copying 10.2.0.3 Config Standard changes from
Rem                           EMCORE_MAIN_LINUX
Rem    niramach    02/28/06 - Add get_context_type function to get the current 
Rem                           context type. 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    jsadras     03/04/05 - purge procedure 
Rem    jsadras     10/25/04 - Add 10G Error 12899
Rem    rpinnama    08/02/04 - Log using autonomous txn 
Rem    jsadras     02/26/04 - jsadras_trace_create 
Rem    jsadras     02/26/04 - emdw_log pkg body 
Rem    jsadras     02/26/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EMDW_LOG
AS
--
-- Internal PROCEDURE to set the Boolean variables
--
PROCEDURE set_boolean_variables
IS
BEGIN
      p_is_error_set := ( lerror <= p_ctx_arr.trace_level) ;
      p_is_warn_Set :=  ( lwarn  <= p_ctx_arr.trace_level) ;
      p_is_info_set :=  ( linfo  <= p_ctx_arr.trace_level) ;
      p_is_debug_set := ( ldebug <= p_ctx_arr.trace_level) ;
      p_is_tracing_set := p_is_error_set ;
END ;
--
-- Internal PROCEDURE to set Default context Values
--
PROCEDURE set_default_context_values
IS
BEGIN
     p_ctx_arr.context_type_id := 0 ;
     p_ctx_arr.context_type := p_default_context_type ;
     p_ctx_arr.trace_level := LOFF ;
END ;
--
-- Internal procedure to get context id for the context type
--
FUNCTION get_context_id(v_context_type IN VARCHAR2)
RETURN NUMBER
IS
l_context_type_id NUMBER ;
BEGIN
      SELECT context_type_id
      INTO l_context_type_id
      FROM emdw_trace_config
      WHERE context_type = upper(v_context_type ) ;
      RETURN(l_context_type_id) ;
EXCEPTION WHEN OTHERS THEN RETURN (-1) ;
END ;

--Purpose   : Returns the current context type.
FUNCTION get_context_type 
RETURN VARCHAR2 
IS
BEGIN
  RETURN p_ctx_arr.context_type;
END;

--
-- PURPOSE:
-- Sets the context type for current session to v_context_type,
-- if v_context_type does not exist then default context is chosen
-- Sets the trace level for session based on  the values in emdw_trace_config table
--
PROCEDURE set_context(v_context_type       IN VARCHAR2 := p_default_context_type,
  	  	      v_context_identifier IN VARCHAR2 := null,
                      v_oms_host           IN VARCHAR2 := null
                    )
IS
BEGIN
   BEGIN
      p_refresh_enabled            := TRUE ;
      SELECT context_type_id,context_type,trace_level,null,null
      INTO p_ctx_arr
      FROM emdw_trace_config
      WHERE context_type = upper(v_context_type ) ;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
        BEGIN
            SELECT context_type_id,context_type,trace_level,null,null
            INTO p_ctx_arr
            FROM emdw_trace_config
            WHERE context_type = p_default_context_type ;
        EXCEPTION
             WHEN OTHERS THEN set_default_context_values ;
        END ;
        WHEN OTHERS THEN set_default_context_values ;
   END ;
   set_boolean_variables ;
   p_ctx_arr.context_identifier := substr(v_context_identifier,1,30);
   p_ctx_arr.oms_host           := substr(v_oms_host,1,100);
EXCEPTION 
  WHEN OTHERS THEN 
     set_default_context_values ;
     set_boolean_variables ; 
END set_context;
--
-- PURPOSE:
--  Gets the trace level for current context from DB
--
PROCEDURE refresh_context
IS
BEGIN
    IF p_refresh_enabled THEN
       set_context(p_ctx_arr.context_type,p_ctx_arr.context_identifier,p_ctx_arr.oms_host) ;
    END IF ;
END refresh_context; 
--
-- PURPOSE
-- Creates a record in emdw_trace_config table for the context type
-- Errors are trapped
-- Sets current context to v_context_type, this will be useful so developer
-- can call create_context and then start logging without calling set_context
-- if context_type already exists then the trace level is set for that.
--
PROCEDURE create_context(v_context_type in VARCHAR2,
                         v_trace_level  in NUMBER := LOFF)
IS
BEGIN
     INSERT INTO emdw_trace_config
     (context_type_id,context_type,trace_level,create_date,last_update_date)
     VALUES
      (emdw_trace_context_type_id_seq.nextval,
       upper(v_context_type),
       v_trace_level,
       sysdate,
       sysdate
      ) ;
      set_context(v_context_type) ;
EXCEPTION
--
--It is possible the dup_val_on_index is due to context_type_id 
--which can be caused by some resetting the sequence
--In this case no_data_found exception will be raised by set_trace_level
--No need for elaborate logic to handle this rare situation
--
    WHEN dup_val_on_index THEN
       IF v_trace_level between LERROR and LDEBUG then
           set_trace_level(v_context_type,v_trace_level) ;
       END IF ; 
END ;
--
--PURPOSE
--Delete the trace data records for the context type
--Delete the trace config record for the context type
--
PROCEDURE delete_context(v_context_type in VARCHAR2)
IS
BEGIN
      --Stop Trace Generation for Future,
      --currently running programs will continue to 
      --generate trace data for the context type

      set_trace_level(v_context_type,LOFF);

      -- Delete trace data for the context type

      purge(v_context_type) ;
       
      DELETE emdw_trace_config
      WHERE context_type = upper(v_context_type) ; 

      COMMIT ;
END ;
--
--PURPOSE
-- Set the trace level for the context  type
-- if context type is not specified, then the trace level is set for all
-- context types
--
PROCEDURE  set_trace_level(v_context_type IN VARCHAR2 := NULL,
                           v_trace_level  IN NUMBER   :=LOFF) 
IS
BEGIN
    IF v_trace_level BETWEEN LOFF AND LDEBUG THEN
        UPDATE emdw_trace_config
        SET trace_level      = v_trace_level,
            last_update_date = sysdate
        WHERE context_type   = nvl(upper(v_context_type ),context_type) ; 
        IF SQL%NOTFOUND THEN RAISE NO_DATA_FOUND ; END IF ;
         refresh_context ;
    ELSE
        RAISE subscript_outside_limit ; 
    END IF ;   
END set_trace_level;
--
--PURPOSE
-- This procedure sets the trace level for current session only
-- It disables refresh_context ,so table values will not be picked up
-- by calls to refresh_context. The trace_level for current session
-- can be changed only by calls to  this procedure or set_context
--
PROCEDURE set_session_trace_level(v_trace_level in NUMBER)
IS
BEGIN
    IF v_trace_level BETWEEN LOFF AND LDEBUG THEN
        p_ctx_arr.trace_level := v_trace_level ;
        set_boolean_variables ;
        p_refresh_enabled     := FALSE ;
    END IF ;
END ;
--
--PURPOSE
--Log the message into the trace_data table based on the following.
--1.Messages are logged only if the log_level specified is less than or equal to the
--  Current trace level.
--2.Messages greater than 1000 Characters will be truncated and stored as seperate records
--3.Error conditions are Logged under tracer context type, if they cannot be logged
--  then they are ignored
--4.Invalid log levels (<0 and >4) are  ignored
--
PROCEDURE log( v_log_level in NUMBER,
               v_log_message IN VARCHAR2,
               v_module IN VARCHAR2 := null
              ) 
IS 
PRAGMA AUTONOMOUS_TRANSACTION;
bigstring_exception1 EXCEPTION ;
PRAGMA exception_init(BigString_EXCEPTION1,-1401) ;
bigstring_exception2 EXCEPTION ;
PRAGMA exception_init(BigString_EXCEPTION2,-12899) ;
l_string VARCHAR2(1000) ;
l_full_length NUMBER := 0 ;
l_iteration NUMBER := 0 ;
BEGIN
    IF v_log_level <= p_ctx_arr.trace_level and 
       (v_log_level between LERROR and LDEBUG) THEN
	BEGIN
             INSERT INTO emdw_trace_data
              (context_type_id,context_identifier,
               log_level, log_timestamp,
               log_message,module,
               oms_host)
             VALUES
              (p_ctx_arr.context_type_id,p_ctx_arr.context_identifier, 
               v_log_level, systimestamp, 
               v_log_message,substr(v_module,1,100),
               p_ctx_arr.Oms_Host) ;
        EXCEPTION
        WHEN bigstring_exception1 or bigstring_exception2 THEN
              l_full_length := length(v_log_message) ;
              WHILE ( l_iteration*1000 <= l_full_length) 
              LOOP
                   l_String := substr(v_log_message,l_iteration*1000+1,1000) ;
                   l_iteration :=l_iteration+1 ;
                   INSERT INTO emdw_trace_data
                   (context_type_id,context_identifier,
                    log_level, log_timestamp,
                    log_message,module,
                    oms_host)
     	            VALUES
      		    (p_ctx_arr.context_type_id,p_ctx_arr.context_identifier,
    		     v_log_level,systimestamp, 
                     l_string, substr(v_module,1,100),
                     p_ctx_arr.oms_host) ;
              END LOOP ; -- BigString
        END ;
      COMMIT;
    END IF ; -- v_log_level <= p_ctx_arr.trace_level
EXCEPTION
-- This Exception would  mostly be due to space issues, so the insert here 
-- would fail too and be caught by the exception when others in inner block
-- One other possible condition is foreign key violation due to invalid
-- context_type_id or 0 records in emdw_trace_config table.
   WHEN others THEN
   DECLARE
        l_trace_context_type_id NUMBER := 0 ;
        l_error_msg VARCHAR2(1000) ;
   BEGIN 
        l_error_msg := substr(sqlerrm,1,1000) ;
        l_trace_context_type_id := get_context_id(p_tracer_context_type) ;
        IF l_trace_context_type_id > 0 THEN
           INSERT INTO emdw_trace_data
           (context_type_id,context_identifier,
            log_level,log_timestamp,
            log_message,module,
            oms_host)
           VALUES
            (l_trace_context_type_id,p_ctx_arr.context_identifier,
                LERROR,systimestamp, 
             l_error_msg,substr(v_module,1,100),
             p_ctx_arr.oms_host) ;
           COMMIT;
         END IF ;
   EXCEPTION WHEN OTHERS THEN ROLLBACK ;
   END ;
END log;
--
--Log a error message
--
PROCEDURE error( v_log_message IN VARCHAR2,
                 v_module IN VARCHAR2
               )
IS
BEGIN
    log(LERROR,v_log_message,v_module) ; 
END error;
--
--Log a debug message
--
PROCEDURE debug(v_log_message IN VARCHAR2,
                 v_module IN VARCHAR2
              )
IS
BEGIN
     log(LDEBUG,v_log_message,v_module) ;
END debug;
--
--Log a warning message
--
PROCEDURE warn(v_log_message IN VARCHAR2,
                 v_module IN VARCHAR2
              )
IS
BEGIN
     log(LWARN,v_log_message,v_module) ;
END warn;
--
--Log a Informational message
--
PROCEDURE info(v_log_message IN VARCHAR2,
               v_module IN VARCHAR2
              )
IS
BEGIN
     log(LINFO,v_log_message,v_module) ;
END info;
--
-- Private procedure
--PURPOSE
-- Purge trace data for the context type (default =all) which were logged
-- before specified purge date (default=all)
-- See package Spec for more details
-- The purge can be monitored by setting the trace level for TRACER context type
-- to LINFO. Errors during purge can be logged by setting trace level to LERROR
-- Info and Error Data will be logged into emdw_trace_data table
--
PROCEDURE purge(v_context_type IN VARCHAR2 := null,
                v_purge_date   IN DATE := sysdate+1,
	        v_batch_size   IN NUMBER := 100000,
                v_rows_purged  OUT NUMBER )
is
TYPE l_rowid_list_type IS  TABLE OF UROWID  INDEX BY BINARY_INTEGER;

--
-- Full tablescan below, we could write seperate cursors for specific
-- context, one for all  contexts, but the maintenance overhead will be more.
-- We should be purging globally most of the time
-- We could also use dynamic cursors to avoid full table scan.
-- Ordered by rowid to avoid 1555 or fetch out of sequence errors
--
CURSOR purgerows_cursor( v_context_type_id IN NUMBER, v_purge_date IN DATE) IS
SELECT ROWID
FROM emdw_trace_data
WHERE log_timestamp < v_purge_date AND
      context_type_id = nvl(v_context_type_id,context_type_id)
ORDER BY ROWID ASC;

l_rows_processed NUMBER := 0 ;
l_instance VARCHAR2(20):= to_char(v_purge_date,'YYYYMMDD#HH24MISS');
l_rowid_list l_rowid_list_type ;
l_batchsize NUMBER(10) ;
l_maxbatchSize NUMBER(10) := 100000000 ;
l_context_type_id NUMBER;
BEGIN
    IF v_batch_size < 1 THEN
       l_batchsize :=1 ;
    ELSE
       l_batchsize:= least(v_batch_size,l_maxbatchsize) ;
    END IF ;

    IF v_context_type is null THEN
          open PurgeRows_Cursor(null,v_purge_date) ;
    ELSE
         l_context_type_id := get_context_id(v_context_type) ;

         -- IF the context_type_id does not exist, raise error 
         -- do not catch exception to let it cascade up

         IF l_context_type_id <0 THEN RAISE no_data_found ; END IF ;

         OPEN purgerows_cursor(l_context_type_id,v_purge_date); 
    END IF ;
    LOOP
       FETCH purgerows_cursor BULK COLLECT INTO l_rowid_list LIMIT l_batchsize ;
       EXIT WHEN l_rowid_list.count <=0 ;
       FORALL i IN l_rowid_list.FIRST..l_rowid_list.LAST
	 DELETE emdw_trace_Data
	 WHERE rowid = l_rowid_list(i) ;
	 l_rows_processed := l_rows_processed + l_rowid_list.count ;
	 IF p_is_debug_set THEN 
           debug('emdw_log_purge:Total rows purged so far='||
                 to_char(l_rows_processed),p_module) ;
         END IF ;
	 COMMIT ;
       l_rowid_list.delete ;
    END LOOP ;
    CLOSE PurgeRows_Cursor  ;
    v_rows_purged := l_rows_processed ;
EXCEPTION 
WHEN OTHERS THEN
    IF p_is_error_set  THEN
         error(sqlerrm,p_module) ;
    END IF ;
    COMMIT ;
    IF PurgeRows_Cursor%isOpen THEN
         close PurgeRows_Cursor ; 
    END IF ;
    RAISE ;
END purge;
--
-- Exposed purge procedure without out parameter ( v_rows_purged)
--
PROCEDURE purge(v_context_type IN VARCHAR2 := null,
                v_purge_date   IN DATE := sysdate+1,
	        v_batch_size   IN NUMBER := 100000
               )
IS
l_rows_purged NUMBER ;
BEGIN
   purge(v_context_type=>v_context_type,
         v_purge_date=>v_purge_Date,
         v_batch_size=>v_batch_size,
         v_rows_purged=>l_rows_purged) ;
END purge ;
--
--Purpose  :  Background purge procedure called by Purge policy
--
PROCEDURE purge(p_purge_params IN  OUT NOCOPY MGMT_PURGE_CALLBACK_PARAMS) 
IS
BEGIN
  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('emdw_log_purge:Enter,Purge Till:'||
                 to_char(p_purge_params.PURGE_UPTO_TIME,'DD-MON-YY HH24:MI'),
                 p_module) ;
  END IF;

  purge(v_purge_date=>p_purge_params.purge_upto_time,
        v_batch_size=>50000,
        v_rows_purged=>p_purge_params.rows_processed) ;

  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('emdw_log_purge:Exit Normal,Rows Purged='||
                  p_purge_params.rows_processed,p_module) ;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('emdw_log_purge:Exit Exception '||sqlerrm,p_module) ;
  END IF;
  -- it is expected that the calling purge logs this exception
  RAISE ; 
END purge ;

BEGIN
-- Set the context type to default on first call to this package in the session
   set_context(p_default_context_type) ;
END ;
/

show errors