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)
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.