create or replace package body my_xplan_generator
is
procedure generate_xplan_sql_file(package_name varchar2, spool_location varchar2 )
is
type sql_information is record(sql_id varchar2(100),child_number number, query_name varchar2(4000));
type sql_info_tab is table of sql_information;
l_hints text_tab;
l_sql_ids sql_info_tab;
l_child_number number;
--package name are used to look up the named queries
-- expected format is xplan_<package_name><identifier>
-- e.g. xplan_some_api_pkg01
-- note if using numbers for identifier two digits is preferred as identifier xplan_some_api_pkg1 will return xplan_some_api_pkg10 etc.
-- other hints can be used but they must be used before the query name, not after
l_file_name varchar2(1000);
begin
--identify the sql id of the particular query using the comment as a lookup
--get the last active version as the sql may have been changed in development
select sql_id, child_number, query_name
bulk collect
into l_sql_ids
from (select sql_id,child_number,
'xplan_my_test_pkg' query_name,
row_number() over(order by last_active_time desc) rn
from v$sql s
where lower(sql_text) like '%xplan_my_test_pkg%'
and lower(sql_text) not like '%v$sql%'
and lower(sql_text) not like '%dba_source%'
and upper(sql_text) not like '%THIS QUERY WAS NOT RUN TODAY%'
--expected to be run with the unit test suite so will only get todays runs
and trunc(last_active_time) = trunc(sysdate)
)
where rn = 1;
--output the select statements to an sql file which will then be run to generate the plans
for i in l_sql_ids.first .. l_sql_ids.last loop
if l_sql_ids(i).sql_id is not null then
dbms_output.put_line('spool ' || spool_location || l_sql_ids(i).query_name||'.xplan');
dbms_output.put_line('select * from table(dbms_xplan.display_cursor(
sql_id =>''' || l_sql_ids(i).sql_id || ''',
cursor_child_no =>''' || l_sql_ids(i).child_number || ''',
format => ''ALLSTATS LAST''));');
else
dbms_output.put_line('spool ' || spool_location || l_sql_ids(i).query_name||'NOT_RUN.xplan');
dbms_output.put_line('select '''||l_sql_ids(i).query_name||''' query, ''THIS QUERY WAS NOT RUN TODAY'' note from dual;');
end if;
dbms_output.put_line('spool off');
end loop;
dbms_output.put_line('SUCCESS');
end generate_xplan_sql_file;
end my_xplan_generator;