create or replace package my_test_pkg 
is 
procedure my_test_proc; 
end my_test_pkg; 

create or replace package body my_test_pkg 
is 
 
procedure my_test_proc 
is 
lv_count pls_integer; 
begin 
    select /*+ xplan_my_test_pkg01 */  
        count(*) into lv_count 
    from dual; 
end my_test_proc; 
end my_test_pkg; 

create or replace package my_xplan_generator 
is 
type text_tab is table of varchar2(4000); 
procedure generate_xplan_sql_file(package_name varchar2 , spool_location varchar2 ); 
 
end my_xplan_generator; 

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; 

begin 
my_test_pkg.my_test_proc; 
my_xplan_generator.generate_xplan_sql_file('',''); 
end; 

select * from table(dbms_xplan.display_cursor(  
                     sql_id =>'7b38a7bfurqby',  
                     cursor_child_no =>'0',  
                     format => 'ALLSTATS LAST'))