Rem
Rem $Header: sdk_sqltrace_pkgbody.sql 15-jul-2005.05:31:16 scgrover Exp $
Rem
Rem sdk_sqltrace_pkgbody.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_sqltrace_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    scgrover    07/15/05 - scgrover_add_dbms_job_extended_sql_trace
Rem    scgrover    07/06/05 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_SQLTRACE AS

PROCEDURE EXTENDED_SQL_TRACE(p_trace_name IN VARCHAR2)
IS
  traceON      VARCHAR2(256) := EST_OFF;
  instanceName VARCHAR2(16);
  instanceNum  NUMBER;
  sessionID    NUMBER;
  processID    NUMBER;
BEGIN

  BEGIN 
    SELECT upper(parameter_value) INTO traceON FROM mgmt_parameters
    WHERE parameter_name = p_trace_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO mgmt_parameters(parameter_name, parameter_value, parameter_comment)
      VALUES(p_trace_name, EST_OFF, 'Extended SQL trace description needs to be added');
      COMMIT;
      traceON := EST_OFF;
  END; 

  IF traceON = EST_ON THEN

    SELECT instance_name, instance_number INTO instanceName, instanceNum FROM v$instance;
    SELECT sid INTO sessionID FROM v$mystat WHERE rownum = 1;
   
    SELECT p.spid INTO processID FROM v$process p, v$session s WHERE p.addr = s.paddr
      AND s.sid = sessionID;

    EXECUTE IMMEDIATE 'alter session set statistics_level = all';
    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';

    MGMT_LOG.LOG_ERROR('EXTENDED SQL TRACE', null, 'Tracing system ' || p_trace_name || ' on instance ' ||
                       instanceName || ' (number ' || instanceNum || ') using session id ' || sessionID || 
                       ' process id ' || processID || ' look for a file named ' || instanceName || 
                       '_j???_' || processID || '.trc in the bdump location');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR('EXTENDED SQL TRACE', null, 'cannot turn trace on for system ' ||
        p_trace_name || ' ' || SUBSTR(SQLERRM, 1, 900));
END EXTENDED_SQL_TRACE;

PROCEDURE EXTENDED_SQL_TRACE_ON(p_trace_name IN VARCHAR2, p_value IN BOOLEAN)
IS
BEGIN
  IF p_value THEN
    UPDATE mgmt_parameters SET parameter_value = EST_ON
    WHERE parameter_name = p_trace_name;
  ELSE
    UPDATE mgmt_parameters SET parameter_value = EST_OFF
    WHERE parameter_name = p_trace_name;
  END IF;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR('EXTENDED SQL TRACE', null, 'cannot update extended sql trace state for system ' ||
        p_trace_name || ' ' || SUBSTR(SQLERRM, 1, 900));
END EXTENDED_SQL_TRACE_ON;

END MGMT_SQLTRACE;
/
show errors;