PL/SQL Unit Test for SQL geneartor

If PL/SQL is unit tested utPLSQL (http://utplsql.org/utPLSQL/) is probably the most majure solution to implement it.
Here I suggest a solution to check if generated SQL is executable.

We have a package P_SERCH whith a function generate_sql to generate dynamic sql.
Since there is no native execption checking in utPLSQL we execute the generated SQL in a block.
On a error we to a False comparsion “ut.expect(‘ok’).to_equal(‘error’)”.
If the statement executes without error we to a True comparsion “ut.expect(‘ok’).to_equal(‘error’)”

To lower the impact of the sql execution the statement is wrap in a count select.
If theh statement execution is still inappropriate for performance reasons this could be replaced as well with a DBMS_SQL parse.

  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, 'select * from multi_tab order by 1', DBMS_SQL.NATIVE);

Package Header and Body of the unit test:

create or replace package ut3_P_SEARCH as
  -- %suite(p_search)
  -- %suitepath(all.online)

  --%beforeall
  procedure global_setup;

  --%afterall
  procedure global_cleanup;

  --%beforeeach
  procedure test_setup;

  --%aftereach
  procedure test_cleanup;
 
  -- %test
  -- %displayname(generate solr sql)
  procedure gensql_starship;

end ut3_P_SEARCH;
/


create or replace package body ut3_P_SEARCH as
  procedure global_setup is
  begin
    null;
  end;

  procedure test_setup is
  begin
    null;
  end test_setup;

  procedure global_cleanup is
  begin
    null;
  end;

  procedure test_cleanup is
  begin
    null;
  end test_cleanup;


  procedure gensql_starship is
    v_sql                 varchar2(4000);
  begin
    -- retrieve generated sql for person class
    select scott.p_search.generate_sql('STARSHIP') into v_sql from dual;
    -- check if executable
    begin
      execute immediate 'select count(*) from ('||v_sql||')';
      ut.expect('ok').to_equal('ok');
    exception  when others then
      ut.expect('ok').to_equal('error');
    end;
    -- check tables 
    ut.expect(v_sql).to_be_like(a_mask=>'%'|| p_metadata_util.f_class_relational_name('PERSON') ||'%');
    -- check static attributes
    ut.expect(v_sql).to_be_like(a_mask=>'%"id"%');
  END gensql_person;


end ut3_P_SEARCH;
/

test execution
SQL> begin ut.run('scott.ut3_p_search.gensql_starship'); end;
  2  /
all
online
p_search
generate solr sql
Finished in .014174 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

PL/SQL procedure successfully completed.

A good source for PL/SQL Testing and Error handling is Jacek Gebals blog (http://www.oraclethoughts.com/), he is also one of the main developer of utPLSQL (version 3)

Find Oracle SQL Profiles causing dynamic_sampling

Recently I hat memory lock issues with statements using dynamic sampling.
But after disabling dynamic_sampling at the system level there were still some statements causing issues with dynamic_sampling.
The execution plan showed that the problematic statements had SQL Profiles. Thanks to a blog from
Cristian Antognini (https://antognini.ch/2008/08/sql-profiles-in-data-dictionary/) all SQL Profiles with with a dynaimic sampling hint could be found.

Demo: Setup a statment with a sqlprofile

exec dbms_sqltune.drop_sql_profile('SQLPROFILE42');
DECLARE
     l_sql               clob;
     BEGIN
     l_sql := q'!select id from t42 where id=:x!';

     dbms_sqltune.import_sql_profile( sql_text => l_sql, 
                                     name => 'SQLPROFILE42',
                                     profile => sqlprof_attr(q'!OPT_PARAM('optimizer_dynamic_sampling' 2)!'
                                                -- ,q'!FULL(@"SEL$1" "T42"@"SEL$1")!'
                                                ,q'!INDEX(@"SEL$1" "T42"@"SEL$1" "I42_3")!'
             ),
             force_match => true );
     end;
/


var x number;
exec :x := 2167658022;

explain plan for select id from t42 where id=:x;
select * from table(dbms_xplan.display(null,null,'ADVANCED'));

Finding the SQL Profiles with dynamic sampling

 
SELECT so.name,extractValue(value(h),'.') AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
WHERE 1=1 -- so.name = '&sqlprof'
  AND so.signature = od.signature
  AND so.category = od.category
  AND so.obj_type = od.obj_type
  AND so.plan_id = od.plan_id
  and extractValue(value(h),'.') like '%dynamic%'
;
HINT
----------------------------------------------------------------------------------------------------
OPT_PARAM('optimizer_dynamic_sampling' 2)

Will there be Oracel RDBMS 12.2 for ODA X6?

Will there be Oracel RDBMS 12.2 for ODA X6?

Doc ID 888888.1

* Currently X7-2 is released with 12.2.1.1. but will not be provided for other ODA HW Types.
   The next bundle 12.2.1.2 (pending) will be released for all other versions X6.2*, X5-2,X4-2, X3-2, and V1.

So it seems Oracle 12.2 is comming for the ODA x6 as well in the near future.