SET SERVEROUTPUT ON SIZE 1000000;
-- Test ctl_gen:
DECLARE
l_mapping_1 VARCHAR2(30) := 'TEST_MAP_1';
l_mapping_2 VARCHAR2(30) := 'TEST_MAP_2';
l_run_no_1 NUMBER;
l_run_no_2 NUMBER;
l_end_date_1 VARCHAR2(30);
l_end_date_2 VARCHAR2(30);
l_row_count NUMBER;
l_row_data VARCHAR2(4000);
l_passed BOOLEAN := TRUE;
BEGIN
dbms_output.put_line('-');
dbms_output.put_line('- Initialise...');
-- Initialisation code.
DELETE FROM mapping_histories
WHERE mapping_library IN (l_mapping_1, l_mapping_2);
DELETE FROM mapping_libraries
WHERE mapping_name IN (l_mapping_1, l_mapping_2);
INSERT INTO mapping_libraries
(
mapping_name
,rep_from
,rep_to
)
VALUES
(
l_mapping_1
,'CONTROL'
,'CONTROL'
);
INSERT INTO mapping_libraries
(
mapping_name
,rep_from
,rep_to
)
VALUES
(
l_mapping_2
,'CONTROL'
,'CONTROL'
);
COMMIT;
dbms_output.put_line('- Done.');
dbms_output.put_line('-');
dbms_output.put_line('+- PROCEDURE initialise_mapping test ' ||
'start:');
dbms_output.put_line('|');
dbms_output.put_line
('| Test 1: Pass ''TEST_MAP_1'': General test.');
dbms_output.put_line('|');
dbms_output.put_line
('| Expected result: Returns a Run number.');
dbms_output.put_line
('| Inserts a row into MAPPING_HISTORIES.');
dbms_output.put_line('|');
ctl_gen.initialise_mapping(l_mapping_1, l_run_no_1);
dbms_output.put_line('| Result: Run number = ' || l_run_no_1);
SELECT COUNT(*)
INTO l_row_count
FROM mapping_histories
WHERE mapping_library = l_mapping_1;
dbms_output.put_line(
'| MAPPING_HISTORIES row count = ' ||
l_row_count);
dbms_output.put_line('| MAPPING_HISTORIES data :');
SELECT '| [' || run_no || ', ' ||
TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ', ' ||
NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), 'NULL') ||
', ' || mapping_library || ']'
INTO l_row_data
FROM mapping_histories
WHERE mapping_library = l_mapping_1;
dbms_output.put_line(l_row_data);
dbms_output.put_line('|');
dbms_output.put_line('| +----------+');
IF l_run_no_1 > 0
AND
l_row_count = 1
THEN
dbms_output.put_line('| | PASSED |');
ELSE
dbms_output.put_line('| | FAILED |');
l_passed := FALSE;
END IF;
dbms_output.put_line('| +----------+');
dbms_output.put_line('|');
dbms_output.put_line('+- PROCEDURE initialise_mapping test end:');
dbms_output.put_line('+');
dbms_output.put_line('+- PROCEDURE finalise_mapping test start:');
dbms_output.put_line('|');
dbms_output.put_line
('| Test 1: Pass ''TEST_MAP_1'': General test.');
dbms_output.put_line('|');
dbms_output.put_line
('| Expected result: Updates MAPPING_HISTORIES with an end ' ||
'date for ' || 'target');
dbms_output.put_line('| mapping.');
dbms_output.put_line('|');
ctl_gen.initialise_mapping(l_mapping_2, l_run_no_2);
ctl_gen.finalise_mapping(l_run_no_1);
dbms_output.put_line('| Result: MAPPING_HISTORIES data :');
SELECT '| [' || run_no || ', ' ||
TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') ||
', ' ||
NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), 'NULL') ||
', ' || mapping_library || ']'
INTO l_row_data
FROM mapping_histories
WHERE mapping_library = l_mapping_1;
dbms_output.put_line(l_row_data);
SELECT '| [' || run_no || ', ' ||
TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ', ' ||
NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), 'NULL') ||
', ' ||
mapping_library || ']'
INTO l_row_data
FROM mapping_histories
WHERE mapping_library = l_mapping_2;
dbms_output.put_line(l_row_data);
SELECT TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS')
INTO l_end_date_1
FROM mapping_histories
WHERE mapping_library = l_mapping_1;
dbms_output.put_line('| End date (' || l_mapping_1 ||
') = ' || l_end_date_1);
SELECT TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS')
INTO l_end_date_2
FROM mapping_histories
WHERE mapping_library = l_mapping_2;
dbms_output.put_line('| End date (' || l_mapping_2 ||
') = ' || NVL(l_end_date_2, 'NULL'));
dbms_output.put_line('|');
dbms_output.put_line('| +----------+');
IF l_end_date_1 IS NOT NULL
AND
l_end_date_2 IS NULL
THEN
dbms_output.put_line('| | PASSED |');
ELSE
dbms_output.put_line('| | FAILED |');
l_passed := FALSE;
END IF;
dbms_output.put_line('| +----------+');
dbms_output.put_line('|');
dbms_output.put_line('+- PROCEDURE finalise_mapping test end:');
dbms_output.put_line('-');
dbms_output.put_line('- Overall result:');
dbms_output.put_line('-');
dbms_output.put_line('- +----------+');
IF l_passed = TRUE
THEN
dbms_output.put_line('- | PASSED |');
ELSE
dbms_output.put_line('- | FAILED |');
END IF;
dbms_output.put_line('- +----------+');
dbms_output.put_line('-');
dbms_output.put_line('- Clean up...');
-- Clean up code.
DELETE FROM mapping_histories
WHERE mapping_library IN (l_mapping_1, l_mapping_2);
DELETE FROM mapping_libraries
WHERE mapping_name IN (l_mapping_1, l_mapping_2);
COMMIT;
dbms_output.put_line('- Done.');
dbms_output.put_line('-');
END;
/
|
0 Comments:
Post a Comment
<< Home