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)