-- Demo case: Researching PL/SQL cursors in case of cardinality feedback / statistics feedback	
-- Date: 19-11-2016
-- Info: Access to sys.dbms_lock needs to be granted (grant execute on dbms_lock to <schema>)
-- Demo run on Oracle version: 12.2.0.1 (Oracle Database 12c EE Extreme Perf Release) and OEL 3.8.13-68.2.2.2.el6uek.x86_64, but compatible with Oracle version >= 12.1

-- Format output
col sql_text format a140
col user_name format a30
col cursor_type format a30

-- create demo table and index
drop table demo1 purge;
create table demo1 as select rownum as n, rpad('*',100,'*') as pad from dual connect by level <= 10000;
create index demo1_i on demo1(n);
execute dbms_stats.gather_table_stats(user,'demo1');

-- create sql dependencies 
create or replace type number_t as table of number;
/

create or replace package feedback_sql 
is
  function f(p_count number) return number_t pipelined;
  procedure do_sql_static(p_sleep number);
  procedure do_sql_dynamic(p_sleep number);
end;
/

create or replace package body feedback_sql is
function f(p_count number) return number_t pipelined is
begin
  for i in 1..p_count
  loop
     pipe row(i);
  end loop;
  return;
end;

procedure do_sql_static(p_sleep number) is
  type t_n is table of number;
  l_n t_n;
  type t_pad is table of varchar2(100);
  l_pad t_pad;
begin
  for i in 1..1000
  loop
    select /*+ called by PL/SQL procedure */ n, pad bulk collect into l_n, l_pad from demo1, table(feedback_sql.f(10)) f where demo1.n = f.column_value;
    sys.dbms_lock.sleep(p_sleep);
  end loop;
end;

procedure do_sql_dynamic(p_sleep number) is
  type sqlcurtyp  is ref cursor;
  v_sql_cursor    sqlcurtyp;
  demo1_record    demo1%rowtype;
  v_stmt_str      varchar2(200);
begin
  v_stmt_str := 'select /*+ called by PL/SQL procedure as dynamic */ n, pad from demo1, table(feedback_sql.f(10)) f where demo1.n = f.column_value';
  for i in 1..1000
  loop
    open v_sql_cursor for v_stmt_str;
    loop
      fetch v_sql_cursor into demo1_record;
      exit when v_sql_cursor%notfound;
    end loop;
    close v_sql_cursor;
    sys.dbms_lock.sleep(p_sleep);
  end loop;
end;

end;
/

-- Prepare test case
alter system flush shared_pool;

ACCEPT continue CHAR PROMPT 'Press ENTER to continue with test case for PL/SQL package procedure with static SQL ...' HIDE

-- Run test case first time as PL/SQL package procedure with static SQL
exec feedback_sql.do_sql_static(0);

-- Get information about cursor(s)
select sql_id, sql_text, child_number, child_address, is_reoptimizable, executions from v$sql where sql_id = 'ftfsqyyf272nz';
select sid, user_name, sql_id, cursor_type, child_address from v$open_cursor where sql_id = 'ftfsqyyf272nz';

-- Run test case second time as PL/SQL package procedure with static SQL
exec feedback_sql.do_sql_static(0);

-- Get information about cursor(s)
select sql_id, sql_text, child_number, child_address, is_reoptimizable, executions from v$sql where sql_id = 'ftfsqyyf272nz';
select sid, user_name, sql_id, cursor_type, child_address from v$open_cursor where sql_id = 'ftfsqyyf272nz';

ACCEPT continue CHAR PROMPT 'Press ENTER to continue with test case for anonymous PL/SQL block with static SQL ...' HIDE

-- Run test case first time as anonymous PL/SQL block with static SQL
declare
  type t_n is table of number;
  l_n t_n;
  type t_pad is table of varchar2(100);
  l_pad t_pad;
begin
  for i in 1..1000
  loop
    select /*+ called by anonymous PL/SQL block */ n, pad bulk collect into l_n, l_pad from demo1, table(feedback_sql.f(10)) f where demo1.n = f.column_value;
    sys.dbms_lock.sleep(0);
  end loop;
end;
/

-- Get information about cursor(s)
select sql_id, sql_text, child_number, child_address, is_reoptimizable, executions from v$sql where sql_id = '65k0mkzwbp9xg';
select sid, user_name, sql_id, cursor_type, child_address from v$open_cursor where sql_id = '65k0mkzwbp9xg';

-- Run test case second time as anonymous PL/SQL block with static SQL
declare
  type t_n is table of number;
  l_n t_n;
  type t_pad is table of varchar2(100);
  l_pad t_pad;
begin
  for i in 1..1000
  loop
    select /*+ called by anonymous PL/SQL block */ n, pad bulk collect into l_n, l_pad from demo1, table(feedback_sql.f(10)) f where demo1.n = f.column_value;
    sys.dbms_lock.sleep(0);
  end loop;
end;
/

-- Get information about cursor(s)
select sql_id, sql_text, child_number, child_address, is_reoptimizable, executions from v$sql where sql_id = '65k0mkzwbp9xg';
select sid, user_name, sql_id, cursor_type, child_address from v$open_cursor where sql_id = '65k0mkzwbp9xg';

ACCEPT continue CHAR PROMPT 'Press ENTER to continue with test case for PL/SQL package procedure with dynamic SQL ...' HIDE

-- Run test case first time as PL/SQL package procedure with dynamic SQL
exec feedback_sql.do_sql_dynamic(0);

-- Get information about cursor(s)
select sql_id, sql_text, child_number, child_address, is_reoptimizable, executions from v$sql where sql_id = '2km5gbx8gmnvt';
select sid, user_name, sql_id, cursor_type, child_address from v$open_cursor where sql_id = '2km5gbx8gmnvt';

-- Run test case second time as PL/SQL package procedure with dynamic SQL
exec feedback_sql.do_sql_dynamic(0);

-- Get information about cursor(s)
select sql_id, sql_text, child_number, child_address, is_reoptimizable, executions from v$sql where sql_id = '2km5gbx8gmnvt';
select sid, user_name, sql_id, cursor_type, child_address from v$open_cursor where sql_id = '2km5gbx8gmnvt';