create or replace package test_utils authid DEFINER is 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; / create or replace package body test_utils 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); 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; / create or replace function clean_twitter_handle ( p_handle in varchar2 ) return varchar2 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; / begin test_utils.initialize_test_cases_for ('CLEAN_TWITTER_HANDLE', null); 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 @ with @sfonplsql', p_expected =>'sfonplsql' ); test_utils.add_test_case ( p_test_case_name =>'Leading / with /sfonplsql', p_expected =>'sfonplsql' ); test_utils.add_test_case ( p_test_case_name =>'Full URL with https://twitter.com/sfonplsql', p_expected =>'sfonplsql' ); test_utils.generate_tester_for_deterministic_function( p_function_name => 'CLEAN_TWITTER_HANDLE', p_package_name => null, p_overload => null, p_owner => user ); end; /