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)

One thought on “PL/SQL Unit Test for SQL geneartor”

  1. I would remove the NULL setups as in utPLSQL v3 those are all optional.
    Also, you don’t need to catch an exception. Test will fail if exception is thrown
    So your example code could be significantly simplified now to:

    — %test(generate solr sql)
    procedure gensql_starship;

    procedure gensql_starship is
    v_sql varchar2(4000);
    begin
    — Act
    select scott.p_search.generate_sql(‘STARSHIP’) into v_sql from dual;
    –Assert
    — 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”%’);
    execute immediate ‘select count(*) from (‘||v_sql||’)’;
    end;

    Additionally in v3.1 we’ve added –%throws annotation (further enhanced in 3.1.2) so if you expect your test to succeed on exception, you can test that the test will throw an exception.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.