/*
	set_sql_trace

	copyright 1999,2004 enquery incorporated
	www.enquery.com
	888.512.2048
	mcstock@enquery.com

	user assumes all liability

	example function to set sql trace in the current or another session
	and return the tracefile name

	must be created in the SYS account, or (for purists), the owning
	account must have explicit (direct, non-roll) privileges granted on
	the references SYS objects
*/

CREATE OR REPLACE FUNCTION -- <version>00.00.00.00</version>
                           set_sql_trace (
   ip_on         IN   BOOLEAN
  ,ip_sid        IN   NUMBER DEFAULT 0
  ,ip_serial#    IN   NUMBER DEFAULT 0
  ,ip_osblocks   IN   NUMBER DEFAULT 0
)
   RETURN VARCHAR2
IS
   i_cursor            INTEGER;
   v_switch            VARCHAR2 (5);
   v_user_dump_dest    v$parameter.VALUE%TYPE;
   v_trace_file_name   VARCHAR2 (200);
   v_sid               v$session.sid%TYPE;
   v_serial#           v$session.serial#%TYPE;

   PROCEDURE exec_sql (
      ip_sql   IN   VARCHAR2
   )
   IS
      i_rows   INTEGER;
   BEGIN
      DBMS_SQL.parse (
         i_cursor
        ,ip_sql
        ,DBMS_SQL.native
      );
      i_rows := DBMS_SQL.EXECUTE (
                   i_cursor
                );
   END;
BEGIN
   -- set switch
   IF ip_on
   THEN
      v_switch := 'TRUE';
   ELSE
      v_switch := 'FALSE';
   END IF;

   -- check if this is for current session, or for a specific session
   -- based on whether an SID was passed in ip_sid
   IF ip_sid = 0
   THEN
      SELECT --+ set_sql_trace()
             sid
            ,serial#
        INTO v_sid
            ,v_serial#
        FROM v$session
       WHERE audsid = USERENV (
                         'SESSIONID'
                      );
   ELSE
      v_sid := ip_sid;
      v_serial# := ip_serial#;
   END IF;

   IF ip_sid = 0
   THEN
      -- set sql_trace for current session
      i_cursor := DBMS_SQL.open_cursor;
      exec_sql (
            'alter session set timed_statistics = '
         || v_switch
      );
      exec_sql (
            'alter session set sql_trace = '
         || v_switch
      );

      IF ip_osblocks > 0
      THEN
         exec_sql (
               'alter session set max_dump_file_size = '
            || ip_osblocks
         );
      END IF;

      DBMS_SQL.close_cursor (
         i_cursor
      );
   ELSE
      -- set sql_trace for specified session
      DBMS_SYSTEM.set_sql_trace_in_session (
         v_sid
        ,v_serial#
        ,ip_on
      );
   END IF;

   SELECT --+ set_sql_trace()
          p.VALUE
     INTO v_user_dump_dest
     FROM v$parameter p
    WHERE p.name = 'user_dump_dest';

   IF INSTR (
         v_user_dump_dest
        ,'\'
      ) > 0
   THEN
      -- NT format 
      SELECT --+ set_sql_trace()
                v_user_dump_dest
             || '\ORA'
             || LTRIM (
                   TO_CHAR (
                      TO_NUMBER (
                         proc.spid
                      )
                     ,'00000'
                   )
                )
             || '.TRC'
        INTO v_trace_file_name
        FROM v$session sess
            ,v$process proc
       WHERE sess.sid = v_sid
         AND sess.serial# = v_serial#
         AND sess.paddr = proc.addr -- this session's server
                                   ;
   ELSIF INSTR (
            v_user_dump_dest
           ,'/'
         ) > 0
   THEN
      -- UNIX format:  ora_67758_ibm06.trc
      SELECT --+ set_sql_trace()
                v_user_dump_dest
             || '/ora_'
             || LTRIM (
                   TO_CHAR (
                      TO_NUMBER (
                         proc.spid
                      )
                     ,'00000'
                   )
                )
             || '_'
             || inst.instance_name
             || '.trc'
        INTO v_trace_file_name
        FROM v$session sess
            ,v$process proc
            ,v$instance inst
       WHERE sess.sid = v_sid
         AND sess.serial# = v_serial#
         AND sess.paddr = proc.addr -- this session's server
                                   ;
   ELSE
      -- unknown OS, return search string: *** SESSION ID:(7.58) 2001-06-04 14:29:01.257
      SELECT --+ set_sql_trace()
                'findtrc '
             || v_user_dump_dest
             || ' "*** SESSION ID:('
             || LTRIM (
                   TO_CHAR (
                      sid
                   )
                )
             || '.'
             || LTRIM (
                   TO_CHAR (
                      serial#
                   )
                )
             || ') '
             || TO_CHAR (
                   SYSDATE
                  ,'YYYY-MM-DD'
                )
             || '"'
        INTO v_trace_file_name
        FROM v$session sess
       WHERE sess.sid = v_sid
         AND sess.serial# = v_serial#;
   END IF;

   RETURN (v_trace_file_name);
END;
/