create or replace package test_utils authid DEFINER
is
type test_case_rt is record (
test_case_name varchar2(1000),
expected_value varchar2(4000)
);
type test_cases_t is
table of test_case_rt index by pls_integer;
type test_run_rt is record (
function_name varchar2(1000),
package_name varchar2(1000),
overload integer,
owner varchar2(1000),
test_cases test_cases_t
);
type test_runs_by_program_t is
table of test_run_rt index by varchar2(1000);
g_test_runs test_runs_by_program_t;
g_function_name varchar2(1000);
g_function_name_for_index varchar2(1000);
k_testing_template constant varchar2(32767) := q'[
declare
k_program constant varchar2(100) := '#PROGRAM_NAME#';
type results_t is
table of varchar2(1000) index by pls_integer;
l_results results_t;
l_success boolean := true;
l_failure_count integer := 0;
procedure test_it (
p_test_case in varchar2,
#PARAMETER_LIST#,
p_expected in varchar2,
p_show_success in boolean default true
) is
l_value varchar2(1000);
begin
l_value := #PROGRAM_NAME# (#ARGUMENT_LIST#);
if l_value = p_expected or (
l_value is null and p_expected is null
) then
if p_show_success then
l_results(l_results.count + 1) := p_test_case || ' succeeded';
end if;
else
l_results(l_results.count + 1) := p_test_case || ' failed';
l_success := false;
l_failure_count := l_failure_count + 1;
end if;
end;
procedure summarize_test is
l_title varchar2(1000) := lpad('*', 5, '*')
|| ' Results from testing '
|| k_program
|| ' '
|| lpad('*', 5, '*');
begin
DBMS_OUTPUT.put_line(l_title);
DBMS_OUTPUT.put_line('*');
if l_success then
DBMS_OUTPUT.put_line('* SUCCESS - All test cases passed.');
else
DBMS_OUTPUT.put_line('* FAILURE - '
|| l_failure_count
|| ' test cases failed.');
end if;
DBMS_OUTPUT.put_line('*');
for indx in 1..l_results.count loop
DBMS_OUTPUT.put_line('* ' || l_results(indx));
end loop;
DBMS_OUTPUT.put_line('*');
DBMS_OUTPUT.put_line(lpad('*', length(l_title), '*'));
end;
begin
#TEST_CASES#
summarize_test;
end;]';
procedure initialize_test_cases_for (
p_function_name in varchar2,
p_package_name in varchar2,
p_overload in integer default null,
p_owner in varchar2 default user
);
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in varchar2
);
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in number,
p_mask in varchar2 default null
);
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in date,
p_mask in varchar2 default null
);
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in timestamp,
p_mask in varchar2 default null
);
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in boolean
);
/* When you've called init_test_cases_for, this is all you need */
procedure generate_tester_for_deterministic_function;
procedure generate_tester_for_deterministic_function (
p_function_name in varchar2,
p_package_name in varchar2 default null,
p_overload in integer default null,
p_owner in varchar2 default user
);
end;Package created.
Package body
create or replace package body test_utils
is
function full_function_name (
p_function_name in varchar2,
p_package_name in varchar2,
p_overload in integer default null,
p_owner in varchar2 default user
) return varchar2 is
begin
return
case
when p_owner is not null then
p_owner || '.'
end
||
case
when p_package_name is not null then
p_package_name || '.'
end
|| p_function_name
|| case
when p_overload is not null then
'-' || p_overload
end;
end;
procedure initialize_test_cases_for (
p_function_name in varchar2,
p_package_name in varchar2,
p_overload in integer default null,
p_owner in varchar2 default user
) is
begin
g_function_name := full_function_name(
p_function_name => p_function_name,
p_package_name => p_package_name,
p_overload => null,
p_owner => p_owner
);
g_function_name_for_index := full_function_name(
p_function_name => p_function_name,
p_package_name => p_package_name,
p_overload => p_overload,
p_owner => p_owner
);
g_test_runs.delete(g_function_name_for_index);
g_test_runs(g_function_name_for_index).function_name := p_function_name;
g_test_runs(g_function_name_for_index).package_name := p_package_name;
g_test_runs(g_function_name_for_index).overload := p_overload;
g_test_runs(g_function_name_for_index).owner := p_owner;
end;
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in varchar2
) is
begin
g_test_runs(g_function_name_for_index).test_cases(g_test_runs(g_function_name_for_index).test_cases.count + 1).test_case_name := p_test_case_name;
g_test_runs(g_function_name_for_index).test_cases(g_test_runs(g_function_name_for_index).test_cases.count).expected_value := p_expected;
end;
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in number,
p_mask in varchar2 default null
) is
begin
add_test_case(p_test_case_name,
case
when p_mask is null then
to_char(p_expected)
else to_char(p_expected, p_mask)
end
);
end;
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in date,
p_mask in varchar2 default null
) is
begin
add_test_case(p_test_case_name,
case
when p_mask is null then
to_char(p_expected)
else to_char(p_expected, p_mask)
end
);
end;
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in timestamp,
p_mask in varchar2 default null
) is
begin
add_test_case(p_test_case_name,
case
when p_mask is null then
to_char(p_expected)
else to_char(p_expected, p_mask)
end
);
end;
procedure add_test_case (
p_test_case_name in varchar2,
p_expected in boolean
) is
begin
add_test_case(p_test_case_name,
case p_expected
when true then
'TRUE'
when false then
'FALSE'
end
);
end;
/* arg_list types
- PARAMETER_LIST: replicate parameter list
- ARGUMENT_LIST: named notation list
- TC_INPUTS: test_it invocation / example
*/
function arg_list (
p_type in varchar2,
p_owner in varchar2,
p_package_name in varchar2,
p_function_name in varchar2 default null,
p_overload in integer default null,
p_include_return in varchar2
) return varchar2 is
l_list varchar2(32767);
begin
select
listagg(
case p_type
when 'PARAMETER_LIST' then
argument_name
|| ' '
|| in_out
|| ' '
|| data_type
when 'ARGUMENT_LIST' then
argument_name
|| ' => '
|| argument_name
when 'TC_INPUTS' then
argument_name
|| ' => '
|| ''''
|| 'Value_for_'
|| argument_name
|| ''''
end, ', ' || chr(10)) within group(
order by POSITION)
into l_list
from all_arguments
where owner = p_owner
and ( ( package_name is null
and p_package_name is null
and object_name = p_function_name )
or ( package_name = p_package_name
and object_name = p_function_name ) )
and data_level = 0
and position <> 0 -- don't include the return type.
and ( overload = p_overload
or p_overload is null )
and ( p_include_return = 'Y'
or ( p_include_return = 'N'
and argument_name is not null ) );
return l_list;
end;
function in_quotes (
p_string in varchar2
) return varchar2 is
begin
return ''''
|| p_string
|| '''';
end;
function test_cases return varchar2 is
k_template varchar2(500) := 'test_it(p_test_case => #TC_NAME#, p_expected => #TC_EXPECTED#, p_show_success => TRUE, #TC_INPUTS#);';
l_test_cases test_cases_t;
l_return varchar2(32767);
begin
case
when g_function_name_for_index is null then
l_return := k_template;
when g_test_runs(g_function_name_for_index).test_cases.count = 0 then
l_return := k_template;
else
l_test_cases := g_test_runs(g_function_name_for_index).test_cases;
for indx in 1..l_test_cases.count loop
l_return := l_return
||
case
when indx > 1 then
chr(10)
end
|| replace(
replace(k_template, '#TC_NAME#', in_quotes(l_test_cases(indx).test_case_name)),
'#TC_EXPECTED#',
in_quotes(l_test_cases(indx).expected_value)
);
end loop;
end case;
return l_return;
exception
/* No test runs for that name */
when no_data_found then
return k_template;
end;
/* generate test block
Useful for:
* functions that are deterministic (declared or not)
* only IN parameters
* scalar parameter types and return type
*/
/* When you've called init_test_cases_for, this is all you need */
procedure generate_tester_for_deterministic_function is
begin
generate_tester_for_deterministic_function(
p_function_name => g_test_runs(g_function_name_for_index).function_name,
p_package_name => g_test_runs(g_function_name_for_index).package_name,
p_overload => g_test_runs(g_function_name_for_index).overload,
p_owner => g_test_runs(g_function_name_for_index).owner
);
end;
procedure generate_tester_for_deterministic_function (
p_function_name in varchar2,
p_package_name in varchar2 default null,
p_overload in integer default null,
p_owner in varchar2 default user
) is
k_full_program_name constant varchar2(200) := full_function_name(
p_function_name => p_function_name,
p_package_name => p_package_name,
p_overload => null,
p_owner => p_owner
);
l_test_block varchar2(32767);
function arg_list_for (
p_type in varchar2
) return varchar2 is
begin
return arg_list(
p_type => p_type,
p_owner => p_owner,
p_package_name => p_package_name,
p_function_name => p_function_name,
p_overload => p_overload,
p_include_return => 'N'
);
end;
function placeholders_substituted_in (
p_template in varchar2
) return varchar2 is
l_return varchar2(32767);
begin
l_return := replace(p_template, '#PROGRAM_NAME#', k_full_program_name);
l_return := replace(l_return, '#TEST_CASES#', test_cases());
l_return := replace(l_return, '#PARAMETER_LIST#', arg_list_for(p_type => 'PARAMETER_LIST'));
l_return := replace(l_return, '#ARGUMENT_LIST#', arg_list_for(p_type => 'ARGUMENT_LIST'));
l_return := replace(l_return, '#TC_INPUTS#', arg_list_for(p_type => 'TC_INPUTS'));
return l_return;
end;
begin
l_test_block := placeholders_substituted_in(k_testing_template);
dbms_output.put_line(l_test_block);
end;
end; Package Body created.
Let's try it with this function
create or replace function clean_twitter_handle (
p_handle in varchar2
) return varchar2 deterministic
is
begin
return
case
when instr(p_handle, '/') > 0 then
substr(p_handle, instr(p_handle, '/', -1) + 1)
when instr(p_handle, '@') > 0 then
substr(p_handle, instr(p_handle, '@', -1) + 1)
else p_handle
end;
end; Function created.
Generate some test code!
begin
test_utils.initialize_test_cases_for (
p_function_name => 'CLEAN_TWITTER_HANDLE',
p_package_name => null,
p_overload => null,
p_owner => user);
test_utils.add_test_case (
p_test_case_name =>'Just as we want it',
p_expected =>'sfonplsql'
);
test_utils.add_test_case (
p_test_case_name =>'Leading @ from @sfonplsql',
p_expected =>'sfonplsql'
);
test_utils.add_test_case (
p_test_case_name =>'Leading / from /sfonplsql',
p_expected =>'sfonplsql'
);
test_utils.add_test_case (
p_test_case_name =>'Full URL https://twitter.com/sfonplsql',
p_expected =>'sfonplsql'
);
test_utils.generate_tester_for_deterministic_function;
end; Statement processed.
declare k_program constant varchar2(100) := 'APEX_PUBLIC_USER.CLEAN_TWITTER_HANDLE'; type results_t is table of varchar2(1000) index by pls_integer; l_results results_t; l_success boolean := true; l_failure_count integer := 0; procedure test_it ( p_test_case in varchar2, , p_expected in varchar2, p_show_success in boolean default true ) is l_value varchar2(1000); begin l_value := APEX_PUBLIC_USER.CLEAN_TWITTER_HANDLE (); if l_value = p_expected or ( l_value is null and p_expected is null ) then if p_show_success then l_results(l_results.count + 1) := p_test_case || ' succeeded'; end if; else l_results(l_results.count + 1) := p_test_case || ' failed'; l_success := false; l_failure_count := l_failure_count + 1; end if; end; procedure summarize_test is l_title varchar2(1000) := lpad('*', 5, '*') || ' Results from testing ' || k_program || ' ' || lpad('*', 5, '*'); begin DBMS_OUTPUT.put_line(l_title); DBMS_OUTPUT.put_line('*'); if l_success then DBMS_OUTPUT.put_line('* SUCCESS - All test cases passed.'); else DBMS_OUTPUT.put_line('* FAILURE - ' || l_failure_count || ' test cases failed.'); end if; DBMS_OUTPUT.put_line('*'); for indx in 1..l_results.count loop DBMS_OUTPUT.put_line('* ' || l_results(indx)); end loop; DBMS_OUTPUT.put_line('*'); DBMS_OUTPUT.put_line(lpad('*', length(l_title), '*')); end; begin test_it(p_test_case => 'Just as we want it', p_expected => 'sfonplsql', p_show_success => TRUE, ); test_it(p_test_case => 'Leading @ from @sfonplsql', p_expected => 'sfonplsql', p_show_success => TRUE, ); test_it(p_test_case => 'Leading / from /sfonplsql', p_expected => 'sfonplsql', p_show_success => TRUE, ); test_it(p_test_case => 'Full URL https://twitter.com/sfonplsql', p_expected => 'sfonplsql', p_show_success => TRUE, ); summarize_test; end;