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)

Author: trieder

Born with CPP, rased with SQL, living with NVIM View all posts by trieder