Extract HTML Data - extract_html_data_test.sql

SET SERVEROUTPUT ON SIZE 1000000; SET FEEDBACK OFF; BEGIN -- Initialisation code. dbms_output.put_line('-'); dbms_output.put_line('- Initialise...'); END; / DELETE FROM parsed_html WHERE raw_run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name = 'TEST_HTML') / DELETE FROM html_parse_passes WHERE raw_run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name IN ('TEST_HTML')) / DELETE FROM html_page_access WHERE html_page_name IN ('TEST_HTML') / DELETE FROM html_pages WHERE name IN ('TEST_HTML') / DELETE FROM mapping_histories WHERE mapping_library = 'M_TEST_HTML' / DELETE FROM mapping_libraries WHERE mapping_name = 'M_TEST_HTML' / COMMIT / INSERT INTO html_pages ( name ,url ,username ,password ) VALUES ( 'TEST_HTML' ,'http:\\dummy' ,NULL ,NULL ) / INSERT INTO mapping_libraries ( mapping_name ,rep_from ,rep_to ) VALUES ( 'M_TEST_HTML' ,'CONTROL' ,'CONTROL' ) / DECLARE l_parse_run_no NUMBER; l_raw_run_no NUMBER; l_passed BOOLEAN := TRUE; l_data_tab extract_html_data.data_list; BEGIN SELECT run_no_s.NEXTVAL INTO l_raw_run_no FROM DUAL; INSERT INTO mapping_histories ( run_no ,mapping_library ,start_tsp ,end_tsp ) VALUES ( l_raw_run_no ,'M_TEST_HTML' ,SYSDATE ,NULL ); INSERT INTO html_page_access ( run_no ,html_page_name ,status ,start_tsp ) VALUES ( l_raw_run_no ,'TEST_HTML' ,'PARSED' ,SYSDATE ); INSERT INTO html_parse_passes ( parse_run_no ,raw_run_no ,status ,start_tsp ) VALUES ( run_no_s.NEXTVAL ,l_raw_run_no ,'PARSED' ,SYSDATE ); COMMIT; DELETE FROM parsed_html WHERE raw_run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name = 'TEST_HTML'); SELECT parse_run_no ,raw_run_no INTO l_parse_run_no, l_raw_run_no FROM html_parse_passes WHERE parse_run_no = (SELECT MAX(parse_run_no) FROM html_parse_passes WHERE raw_run_no = (SELECT MAX(run_no) FROM html_page_access WHERE html_page_name = 'TEST_HTML' AND status = 'PARSED') AND status = 'PARSED') AND raw_run_no = (SELECT MAX(run_no) FROM html_page_access WHERE html_page_name = 'TEST_HTML' AND status = 'PARSED') AND status = 'PARSED'; INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,1 ,0 ,CHR(60) || 'HTML>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,2 ,0 ,CHR(60) || 'HEAD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,3 ,0 ,'TEST_HTML' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,4 ,0 ,CHR(60) || '/HEAD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,5 ,0 ,CHR(60) || 'BODY>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,6 ,0 ,CHR(60) || 'TABLE>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,7 ,0 ,CHR(60) || 'TR>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,8 ,0 ,CHR(60) || 'TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,9 ,0 ,'test data 1' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,10 ,0 ,CHR(60) || '/TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,11 ,0 ,CHR(60) || '/TR>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,12 ,0 ,CHR(60) || '/TABLE>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,13 ,0 ,CHR(60) || 'TABLE>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,14 ,0 ,CHR(60) || 'TR>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,15 ,0 ,CHR(60) || 'TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,16 ,0 ,'test data 2' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,17 ,0 ,CHR(60) || '/TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,18 ,0 ,CHR(60) || 'TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,19 ,0 ,'test data 3' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,20 ,0 ,CHR(60) || '/TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,21 ,0 ,CHR(60) || '/TR>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,22 ,0 ,CHR(60) || 'TR>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,23 ,0 ,CHR(60) || 'TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,24 ,0 ,'test data 4' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,25 ,0 ,CHR(60) || '/TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,26 ,0 ,CHR(60) || 'TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,27 ,0 ,'test data 5' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,28 ,0 ,CHR(60) || '/TD>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,29 ,0 ,CHR(60) || '/TR>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,30 ,0 ,CHR(60) || '/TABLE>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,31 ,0 ,CHR(60) || '/BODY>' ,SYSDATE ); INSERT INTO parsed_html ( parse_run_no ,raw_run_no ,component_seq ,indent ,html_component ,ins_tsp ) VALUES ( l_parse_run_no ,l_raw_run_no ,32 ,0 ,CHR(60) || '/HTML>' ,SYSDATE ); COMMIT; -- Initialise Done. dbms_output.put_line('- Done.'); dbms_output.put_line('-'); dbms_output.put_line('+- PROCEDURE extract_html_data.extract_table test ' || 'start:'); dbms_output.put_line('|'); dbms_output.put_line ('| Test 1: Pass : 2, ''TEST_HTML'''); dbms_output.put_line('|'); dbms_output.put_line ('| Expected Result: Returns a data table containing 2 rows:'); dbms_output.put_line ('| Row 1: ''test data 2'',''test data 3'''); dbms_output.put_line ('| Row 2: ''test data 4'',''test data 5'''); dbms_output.put_line('|'); BEGIN l_data_tab := extract_html_data.extract_table(2, 'TEST_HTML'); dbms_output.put_line('| Result: Data table:'); dbms_output.put_line('| 1: [' || l_data_tab(1) || ']'); dbms_output.put_line('| 2: [' || l_data_tab(2) || ']'); dbms_output.put_line('|'); dbms_output.put_line('| +----------+'); IF l_data_tab.COUNT = 2 AND l_data_tab(1) = '''test data 2'',''test data 3''' AND l_data_tab(2) = '''test data 4'',''test data 5''' THEN dbms_output.put_line('| | PASSED |'); ELSE dbms_output.put_line('| | FAILED |'); l_passed := FALSE; END IF; dbms_output.put_line('| +----------+'); EXCEPTION WHEN others THEN dbms_output.put_line(SQLERRM); dbms_output.put_line('|'); dbms_output.put_line('| +----------+'); dbms_output.put_line('| | FAILED |'); dbms_output.put_line('| +----------+'); l_passed := FALSE; END; dbms_output.put_line('|'); dbms_output.put_line('+- PROCEDURE extract_html_data.extract_table 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('-'); END; / BEGIN -- Clean up... dbms_output.put_line('-'); dbms_output.put_line('- Clean up...'); END; / DELETE FROM parsed_html WHERE raw_run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name = 'TEST_HTML') / DELETE FROM html_parse_passes WHERE raw_run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name IN ('TEST_HTML')) / DELETE FROM html_page_access WHERE html_page_name IN ('TEST_HTML') / DELETE FROM html_pages WHERE name IN ('TEST_HTML') / DELETE FROM mapping_histories WHERE mapping_library = 'M_TEST_HTML' / DELETE FROM mapping_libraries WHERE mapping_name = 'M_TEST_HTML' / COMMIT / BEGIN -- Clean up done. dbms_output.put_line('- Done.'); dbms_output.put_line('-'); END; /