-- trace.sql

DECLARE
  c NUMBER;
  result BINARY_INTEGER;

  FUNCTION countEmps  RETURN NUMBER
  IS
      empCount NUMBER;
  BEGIN
    SELECT count(*) 
      INTO empCount 
      FROM employee;
    RETURN empCount; 
  END;

BEGIN
     DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.TRACE_ALL_CALLS);
     c := countEmps;
     DBMS_TRACE.CLEAR_PLSQL_TRACE;
     DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.TRACE_ALL_SQL);
     c := countEmps;
     DBMS_TRACE.CLEAR_PLSQL_TRACE;
     DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.TRACE_ALL_LINES);
     c := countEmps;
     DBMS_TRACE.CLEAR_PLSQL_TRACE;
     result := DBMS_PROFILER.START_PROFILER(run_comment => 'countEmps - ' || 
            SYSDATE);
     c := countEmps;
     result := DBMS_PROFILER.STOP_PROFILER;
END;
/
