Generic Harness for Unit Testing
DECLARE
/*
Simple unit test framework for <program_name>
Designed specifically for use with deterministic functions.
Top level block; global variable to determine if this test succeeded for failed.
*/
g_success BOOLEAN DEFAULT TRUE;
-- Declare global variables
PROCEDURE initialize
IS
-- Initialize data for the test
BEGIN
NULL;
END initialize;
-- General reporting utility
PROCEDURE report_failure (description_in IN VARCHAR2)
IS
BEGIN
g_success := FALSE;
DBMS_OUTPUT.put_line (' Failure on test "' || description_in || '"'
);
END report_failure;
/*
For each program, generate a local module to test that program.
These are then each called in the executable section of the main block.
*/
PROCEDURE t_<program_name>
IS
l_success BOOLEAN DEFAULT TRUE;
-- Variable for function return value
l_<program_name> <function_return_datatype>;
PROCEDURE run_test_case (
test_case_name_in IN VARCHAR2
,<program_argument_list>
,expected_value_in IN BOOLEAN
,test_type_in IN VARCHAR2
-- 'EQ' or 'ISNULL' or 'ISNOTNULL'
)
IS
BEGIN
l_<program_name> :=
<program_name> (
<program_argument_list>
);
/*
Check value returned by function against the expected value.
If they don't match, report the failure, providing a test case name.
*/
IF test_type_in = 'EQ'
THEN
IF l_<program_name> != expected_value_in
OR l_<program_name> IS NULL
THEN
l_success := FALSE;
report_failure (test_case_name_in);
END IF;
ELSIF test_type_in = 'ISNULL'
THEN
IF l_<program_name> IS NOT NULL
THEN
l_success := FALSE;
report_failure (test_case_name_in);
END IF;
ELSIF test_type_in = 'ISNOTNULL'
THEN
IF l_<program_name> IS NULL
THEN
l_success := FALSE;
report_failure (test_case_name_in);
END IF;
END IF;
IF l_success
THEN
DBMS_OUTPUT.put_line ( ' Success on test case "'
|| test_case_name_in
|| '"'
);
END IF;
END run_test_case;
BEGIN
DBMS_OUTPUT.put_line ('Testing <program_name>');
-- Make a copy for each test case, change the arguments to match.
run_test_case
(test_case_name_in => '<testcase_description>'
<program_argument_list>
,expected_value_in => EXPECTED_VALUE
,test_type_in => TEST_TYPE -- 'EQ', 'ISNULL' or 'ISNOTNULL'
);
IF l_success
THEN
DBMS_OUTPUT.put_line (' Success!');
END IF;
DBMS_OUTPUT.put_line ('');
END t_<program_name>;
BEGIN
initialize;
t_<program_name>;
IF g_success
THEN
DBMS_OUTPUT.put_line
('Overall test status for <program_name>: SUCCESS');
ELSE
DBMS_OUTPUT.put_line
('Overall test status for <program_name>: FAILURE');
END IF;
END; ORA-06550: line 32, column 16: PLS-00103: Encountered the symbol "<" when expecting one of the following: ( ; is with authid as cluster order using external deterministic parallel_enable pipelined result_cache accessibleMore Details: https://docs.oracle.com/error-help/db/ora-06550
Let's Test element_found
CREATE OR REPLACE FUNCTION element_found (
collection_in IN DBMS_SQL.varchar2s
,value_in IN VARCHAR2
,start_index_in IN BINARY_INTEGER DEFAULT NULL
,end_index_in IN BINARY_INTEGER DEFAULT NULL
,nulls_eq_in IN BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN
/*
Overview:
Returns TRUE if the value is found in the collection between the specified index values.
Parameters:
collection_in
The collection to be searched for the specified value.
value_in
The value to be checked for in the collection.
start_index_in - starting index for search; default is first index in
collection as returned by the FIRST method.
end_index_in - ending index for search; default is first index in
collection as returned by the FIRST method.
nulls_eq_in - if TRUE, then if you pass NULL for value_in and
an element in the collection is NULL, this function
will return TRUE. That is, NULL = NULL in this case.
Dependencies/Restrictions:
It checks for equality of collection elements with an = operator.
The datatype of the collection must, therefore, support that
syntax. This will be true for scalars, for example, but not
for records, object types, etc.
*/
IS
/* Set start and end indexes of search. Do not all these values to fall
outside the first and last indexes in the collection. */
l_start BINARY_INTEGER
:= GREATEST (NVL (start_index_in, collection_in.FIRST)
,collection_in.FIRST
);
l_end BINARY_INTEGER
:= LEAST (NVL (end_index_in, collection_in.LAST), collection_in.LAST);
--
l_index BINARY_INTEGER := l_start;
l_element_found BOOLEAN DEFAULT FALSE;
BEGIN
-- If value is NULL, then return NULL unless NULL=NULL.
IF value_in IS NULL AND NOT (NVL (nulls_eq_in, FALSE))
THEN
l_element_found := NULL;
-- If nothing in collection, the element cannot be found.
ELSIF l_start IS NULL OR l_end IS NULL
THEN
l_element_found := FALSE;
ELSE
-- Scan through the contents of the collection until we are out
-- of elements or we found a match.
WHILE ( l_index IS NOT NULL
AND l_index BETWEEN l_start AND l_end
AND NOT l_element_found
)
LOOP
/* Do we have a match?
Note: we do not assign this expression directly to the
Boolean variable, because it might evaluate to NULL, and
we don't want that outcome passed on to l_element_found. */
IF collection_in (l_index) = value_in
OR ( nulls_eq_in
AND collection_in (l_index) IS NULL
AND value_in IS NULL
)
THEN
l_element_found := TRUE;
END IF;
IF NOT l_element_found
THEN
-- Go to the next element.
l_index := collection_in.NEXT (l_index);
END IF;
END LOOP;
END IF;
RETURN l_element_found;
END element_found; Function created.
DECLARE
g_success BOOLEAN DEFAULT TRUE;
--
g_collection DBMS_SQL.varchar2s;
g_empty_collection DBMS_SQL.varchar2s;
PROCEDURE initialize
IS
BEGIN
g_collection (1) := 'ABC';
g_collection (10) := 'DEF';
g_collection (11) := NULL;
g_collection (100) := '123';
END initialize;
-- General reporting utility
PROCEDURE report_failure (description_in IN VARCHAR2)
IS
BEGIN
g_success := FALSE;
DBMS_OUTPUT.put_line (' Failure on test "' || description_in || '"'
);
END report_failure;
PROCEDURE t_element_found
IS
l_success BOOLEAN DEFAULT TRUE;
-- Variable for function return value
l_element_found BOOLEAN;
PROCEDURE run_test_case (
test_case_name_in IN VARCHAR2
,collection_in DBMS_SQL.varchar2s
,value_in VARCHAR2
,start_index_in BINARY_INTEGER
,end_index_in BINARY_INTEGER
,nulls_eq_in BOOLEAN
,expected_value_in IN BOOLEAN
,test_type_in IN VARCHAR2
-- 'EQ' or 'ISNULL' or 'ISNOTNULL'
)
IS
BEGIN
l_element_found :=
element_found (collection_in => collection_in
,value_in => value_in
,start_index_in => start_index_in
,end_index_in => end_index_in
,nulls_eq_in => nulls_eq_in
);
-- Check value returned by function against the expected value.
-- If they don't match, report the failure, providing a test case name.
IF test_type_in = 'EQ'
THEN
IF l_element_found != expected_value_in
OR l_element_found IS NULL
THEN
l_success := FALSE;
report_failure (test_case_name_in);
END IF;
ELSIF test_type_in = 'ISNULL'
THEN
IF l_element_found IS NOT NULL
THEN
l_success := FALSE;
report_failure (test_case_name_in);
END IF;
ELSIF test_type_in = 'ISNOTNULL'
THEN
IF l_element_found IS NULL
THEN
l_success := FALSE;
report_failure (test_case_name_in);
END IF;
END IF;
END run_test_case;
BEGIN
DBMS_OUTPUT.put_line ('Testing ELEMENT_FOUND');
-- Make a copy for each test case, change the arguments to match.
run_test_case
(test_case_name_in => 'Collection is empty (pass in g_empty_collection)'
,collection_in => g_empty_collection
,value_in => 'VALUE'
,start_index_in => NULL
,end_index_in => NULL
,nulls_eq_in => TRUE
,expected_value_in => FALSE
,test_type_in => 'EQ'
);
run_test_case
(test_case_name_in => 'Search through entire collection for non-NULL value that is in collection'
,collection_in => g_collection
,value_in => 'DEF'
,start_index_in => NULL
,end_index_in => NULL
,nulls_eq_in => TRUE
,expected_value_in => TRUE
,test_type_in => 'EQ'
);
run_test_case
(test_case_name_in => 'Search through entire collection for non-NULL value that is not in collection'
,collection_in => g_collection
,value_in => 'QRS'
,start_index_in => NULL
,end_index_in => NULL
,nulls_eq_in => TRUE
,expected_value_in => FALSE
,test_type_in => 'EQ'
);
run_test_case
(test_case_name_in =>
'Search through subset of collection for non-NULL value that is in collection'
,collection_in => g_collection
,value_in => '123'
,start_index_in => 10
,end_index_in => 100
,nulls_eq_in => TRUE
,expected_value_in => TRUE
,test_type_in => 'EQ'
);
run_test_case
(test_case_name_in => 'Search through subset of collection for non-NULL value that is not in collection'
,collection_in => g_collection
,value_in => '1234'
,start_index_in => 10
,end_index_in => 100
,nulls_eq_in => TRUE
,expected_value_in => FALSE
,test_type_in => 'EQ'
);
run_test_case
(test_case_name_in => 'Start and end values outside range of collection, value in collection'
,collection_in => g_collection
,value_in => '123'
,start_index_in => -1000
,end_index_in => 10000
,nulls_eq_in => TRUE
,expected_value_in => TRUE
,test_type_in => 'EQ'
);
run_test_case
(test_case_name_in => 'Search for NULL value with nulls EQ not true'
,collection_in => g_collection
,value_in => NULL
,start_index_in => NULL
,end_index_in => NULL
,nulls_eq_in => FALSE
,expected_value_in => NULL
,test_type_in => 'ISNULL'
);
run_test_case
(test_case_name_in => 'Search for NULL value with nulls EQ set to true'
,collection_in => g_collection
,value_in => NULL
,start_index_in => NULL
,end_index_in => NULL
,nulls_eq_in => TRUE
,expected_value_in => TRUE
,test_type_in => 'ISNOTNULL'
);
IF l_success
THEN
DBMS_OUTPUT.put_line (' Success!');
END IF;
DBMS_OUTPUT.put_line ('');
END t_element_found;
BEGIN
initialize;
t_element_found;
IF g_success
THEN
DBMS_OUTPUT.put_line
('Overall test status for ELEMENT_FOUND: SUCCESS');
ELSE
DBMS_OUTPUT.put_line
('Overall test status for ELEMENT_FOUND: FAILURE');
END IF;
END; Testing ELEMENT_FOUND
Success!
Overall test status for ELEMENT_FOUND: SUCCESS