CREATE USER test IDENTIFIED BY test;

GRANT CONNECT, RESOURCE TO test;

GRANT SELECT ON V_$TIMER TO test;

CONN test/test

@?/rdbms/admin/proftab.sql

CREATE TABLE test_table
   (n_number int, 
    n_size varchar2(5));

CREATE OR REPLACE PROCEDURE set_size 
   (n_number IN number, n_size OUT varchar2) AS
BEGIN
   IF
      n_number > 100 
   THEN
      n_size := 'Large';
   ELSE
      n_size := 'Small';
   END IF;
END set_size;
/

CREATE OR REPLACE PACKAGE test AS
   PROCEDURE main (n_number IN number, n_size OUT varchar2);
END; 
/

CREATE OR REPLACE PACKAGE BODY test AS
   PROCEDURE dummy_proc (dept# IN number, count# out number) AS
BEGIN
   SELECT round(DBMS_Random.Value(1,200)) INTO count# from dual;
END dummy_proc;

PROCEDURE main (n_number IN number, n_size OUT varchar2) IS
   count# number;
   start_time number;
   end_time number;
BEGIN
   select hsecs into start_time from v$timer;
   set_size(n_number, n_size);
   FOR i IN 1 .. 200 LOOP
      dummy_proc( i, count#);
   END LOOP; -- e loop
   -- Placeholder
   select hsecs into end_time from v$timer; 
END main;
END test;
/

CREATE OR REPLACE TRIGGER trig_bi BEFORE INSERT 
ON test_table FOR EACH ROW
BEGIN
   test.main(:new.n_number, :new.n_size);
END; 
/

SELECT DBMS_PROFILER.START_PROFILER 
   ('TEST: '||to_char(sysdate,'dd-Mon-YYYY hh:mi:ss')) "Return Value" 
  FROM dual;

INSERT INTO test_table (n_number) VALUES (85) ;

COLUMN run_comment FORMAT a40
SELECT runid, run_date, run_comment FROM plsql_profiler_runs;

COLUMN unit_name FORMAT a15
COLUMN occured FORMAT 999999
COLUMN line# FORMAT 99999
COLUMN tot_time FORMAT 999.999999
SET PAGESIZE 100

SELECT u.unit_name, 
       d.line#, 
       d.total_occur occured, 
       (d.total_time/1000000000) tot_time, 
       (d.min_time/1000000000) min_time, 
       (d.max_time/1000000000) max_time 
 FROM 
       plsql_profiler_units u, 
       plsql_profiler_data d 
WHERE 
       d.runid = u.runid 
       AND d.unit_number = u.unit_number
       AND d.total_occur > 0
       AND u.runid = &RUNID
ORDER BY u.unit_number, d.line# ;


COLUMN unit_name FORMAT a10
COLUMN line# FORMAT 99999
COLUMN text FORMAT a65
SET PAGESIZE 100
SET LINESIZE 100

SELECT p.unit_name, 
       p.line# line,  
       substr(s.text, 1,75) text 
FROM
       (
        SELECT u.unit_name, 
               d.TOTAL_OCCUR occured, 
              (d.TOTAL_TIME/1000000000) tot_time, 
               d.line# 
          FROM 
               plsql_profiler_units u, 
               plsql_profiler_data d 
         WHERE 
               d.runid = u.runid 
           AND d.unit_number = u.unit_number 
           AND d.total_occur > 0
           AND u.runid = &RUNID
        ) p, user_source s 
WHERE 
       p.unit_name = s.name(+) 
  AND  p.line# = s.line (+)
ORDER BY p.unit_name, p.line#;


-- Clean Up

CONN / AS SYSDBA

DROP USER TEST CASCADE;