The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_str :='select trunc(sum(total_time/1000000000),2) '||
'from plsql_profiler_units ' ||
'where runid in ( select runid from plsql_profiler_runs ' ||
' where related_run = :RELATED_RUN)';
l_sql_str:='select runid,TO_CHAR(run_date,''DD-MON-RR HH24:MI:SS''),'||
'(select to_char(sum(total_time/1000000000),''999,999.00'') '||
' from plsql_profiler_units u '||
' where r.runid=u.runid), '||
'run_comment||'' '' ||run_comment1 ' ||
'from plsql_profiler_runs r '||
'where related_run=:related_run order by runid';
select instance_name into l_instance
from v$instance;
l_sql_str:='select p.line||decode(d.line#,null,null,'||
'''''), '||
'to_char(d.total_occur,''999,999,999''),'||
'to_char((d.total_time/1000000),''999,999.00'') ,ltrim(p.text) '||
'from plsql_profiler_data d, '||
'dba_source p '||
'where p.line = d.line#(+) '||
'and p.type in (''PACKAGE'',''PACKAGE BODY'',''PROCEDURE'',''FUNCTION'') '||
'and p.owner = :U_OWNER '||
'and p.name = upper(:u_NAME ) '||
-- 'and p.line between :l_line_min and :l_line_max '||
'and d.runid(+) = :RUN_ID '||
'and d.unit_number(+) = :U_NUMBER '||
'and p.type = :U_TYPE '||
'order by p.line ';
l_tmp_str:='select nvl(ROUND(sum(total_time)/1000000000,3),0) '||
'from plsql_profiler_units where runid= :RUN_ID';
l_sql_str:=' SELECT ''''||
''''||runid||'' ''||
''''||TO_CHAR(run_date,''DD-MON-RR HH24:MI:SS'')||
'' ''|| ''''||:l_run_total||'' ''||
''''||run_comment||'' '' ||run_comment1||'' ''||
'' ''
FROM plsql_profiler_runs
WHERE runid = :RUN_ID';
/* l_sql_str:='select unit_number,unit_type,unit_owner,unit_name, ' ||
' unit_timestamp, '||
'to_char((total_time/1000000000),''999,999,999.00''), '||
'to_char(((total_time*100)/(1000000000*:l_run_total)),''999,999,999.00'') '||
',decode(unit_owner,'''||l_db_user||''',
(select text from dba_source where type=unit_type and owner=unit_owner and name=unit_name and line=2),'' '')' ||
'from plsql_profiler_units '||
-- 'where runid = :RUN_ID and total_time > 1000000 '||
'where runid = :RUN_ID '||
'and (total_time*100)/(1000000000*:l_run_total) > :G_CUTOFF_PCT '||
' order by total_time desc';
l_sql_str:='select unit_number,unit_type,unit_owner,unit_name, ' ||
' unit_timestamp, '||
'trunc((total_time/1000000000),2), '||
'trunc(((total_time*100)/(1000000000*:l_run_total)),2) '||
',decode(unit_owner,'''||l_db_user||''',
(select text from dba_source where type=unit_type and owner=unit_owner and name=unit_name and line=2),'' '')' ||
'from plsql_profiler_units '||
'where runid = :RUN_ID '||
'and (total_time*100)/(1000000000*:l_run_total) > :G_CUTOFF_PCT '||
' order by total_time desc';
l_sql_str:='select unit_number,unit_type,unit_owner,unit_name ' ||
'from plsql_profiler_units '||
'where runid = :RUN_ID and total_time > 1000000 '||
'and unit_owner = '''||l_db_user||''' and unit_type IN '||
'(''PACKAGE'',''PACKAGE BODY'',''PROCEDURE'',''FUNCTION'') '||
'and (total_time*100)/(1000000000*:l_run_total) > :G_CUTOFF_PCT '||
' order by total_time desc';
select 'Y' into l_run_ok from all_objects
where owner='SYS' and object_type='PACKAGE' and object_name='DBMS_PROFILER';
select 'Y' into l_run_ok from all_objects
where owner='SYS' and object_type='PACKAGE BODY'
and object_name='DBMS_PROFILER';
select 'Y' into l_run_ok from all_tables
where table_name='PLSQL_PROFILER_RUNS' and rownum =1 and owner like '%';
select 'Y' into l_run_ok from all_tables
where table_name='PLSQL_PROFILER_UNITS' and rownum =1 and owner like '%';
select 'Y' into l_run_ok from all_tables
where table_name='PLSQL_PROFILER_DATA' and rownum =1 and owner like '%';
l_sql_str:='select count(*) from plsql_profiler_runs '||
'where related_run = :RELATED_RUN ';
l_sql_str:='select runid from plsql_profiler_runs '||
'where related_run=:related_run order by runid';
l_sql_str:='select runid from plsql_profiler_runs '||
'where related_run=:related_run order by runid';
l_sql_str:='select runid from plsql_profiler_runs '||
'where related_run=:RELATED_RUN';
l_sql_str:='delete plsql_profiler_data where runid in '||
'(select runid from plsql_profiler_runs where related_run=:RELATED_RUN)';
l_sql_str:='delete plsql_profiler_units where runid in '||
'(select runid from plsql_profiler_runs where related_run=:RELATED_RUN)';
l_sql_str:='delete plsql_profiler_runs where related_run=:RELATED_RUN';
l_sql_str:='delete plsql_profiler_data where runid =:RUN_ID';
l_sql_str:='delete plsql_profiler_units where runid =:RUN_ID';
l_sql_str:='delete plsql_profiler_runs where runid=:RUN_ID';
select upper(user) into l_db_user from dual;