Get HTML - get_html_test.sql

NOTE: Add the passwords for the user connect statements before running.

CONNECT ctl/ SET SERVEROUTPUT ON SIZE 1000000; BEGIN dbms_output.put_line('-'); dbms_output.put_line('Initialise CTL...'); END; / DELETE FROM html_page_access WHERE html_page_name = 'GOOGLE' / COMMIT / BEGIN dbms_output.put_line('Done...'); dbms_output.put_line('-'); END; / CONNECT sta/ SET SERVEROUTPUT ON SIZE 1000000; BEGIN dbms_output.put_line('-'); dbms_output.put_line('Initialise STA...'); END; / DELETE FROM html_pages WHERE name = 'GOOGLE' / INSERT INTO html_pages ( name ,url ,username ,password ) VALUES ( 'GOOGLE' ,'http://www.google.com/' ,NULL ,NULL ) / COMMIT / BEGIN dbms_output.put_line('Done...'); dbms_output.put_line('-'); END; / DECLARE l_web_page_1 VARCHAR2(30) := 'GOOGLE'; l_raw_run_no NUMBER; l_html_page_access_count NUMBER; l_raw_html_count NUMBER; l_row_data VARCHAR2(4000); l_char VARCHAR2(1); l_string VARCHAR2(4000); l_passed BOOLEAN := TRUE; BEGIN dbms_output.put_line('-'); dbms_output.put_line('+- PROCEDURE fetch_html test start:'); dbms_output.put_line('|'); dbms_output.put_line ('| Test 1: General test.'); dbms_output.put_line ('| Pass : web_page = ' || l_web_page_1); dbms_output.put_line ('| Expected result: Inserts a row into HTML_PAGE_ACCESS final status' || ' = FETCHED'); dbms_output.put_line ('| Inserts two rows into RAW_HTML.'); get_html.fetch_html(l_web_page_1); SELECT MAX(run_no) INTO l_raw_run_no FROM mapping_histories; SELECT COUNT(*) INTO l_html_page_access_count FROM html_page_access WHERE run_no = l_raw_run_no; dbms_output.put_line('| Result: HTML_PAGE_ACCESS row count = ' || l_html_page_access_count); dbms_output.put_line('| HTML_PAGE_ACCESS data :'); SELECT '| [' || run_no || ', ' || status || ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ']' INTO l_row_data FROM html_page_access WHERE run_no = l_raw_run_no; dbms_output.put_line(l_row_data); SELECT COUNT(*) INTO l_raw_html_count FROM raw_html WHERE run_no = l_raw_run_no; dbms_output.put_line('| RAW_HTML row count = ' || l_raw_html_count); dbms_output.put_line('|'); dbms_output.put_line('| +----------+'); IF l_html_page_access_count = 1 AND l_raw_html_count = 2 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 fetch_html test end:'); dbms_output.put_line('-'); dbms_output.put_line('+- PROCEDURE parse_html test start:'); dbms_output.put_line('|'); dbms_output.put_line ('| Test 1: General test.'); dbms_output.put_line ('| Pass : NO PARAMETERS'); dbms_output.put_line ('| Expected result: Runs without error.'); dbms_output.put_line('|'); BEGIN get_html.parse_html; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('| +----------+'); dbms_output.put_line('| | FAILED |'); l_passed := FALSE; dbms_output.put_line('| +----------+'); END; dbms_output.put_line('| +----------+'); dbms_output.put_line('| | PASSED |'); dbms_output.put_line('| +----------+'); dbms_output.put_line('|'); dbms_output.put_line('+- PROCEDURE parse_html test end:'); dbms_output.put_line('-'); dbms_output.put_line('+- PROCEDURE get_scec test start:'); dbms_output.put_line('|'); dbms_output.put_line ('| Test 1: General test.'); dbms_output.put_line ('| Pass : ''amp'''); dbms_output.put_line ('| Expected result: Returns ''&''' || '.'); dbms_output.put_line('|'); l_char := get_html.get_scec('amp'); dbms_output.put_line('| Result: returned = ' || l_char); dbms_output.put_line('|'); dbms_output.put_line('| +----------+'); IF l_char = '&' 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 get_scec test end:'); dbms_output.put_line('-'); dbms_output.put_line('+- PROCEDURE replace_scec test start:'); dbms_output.put_line('|'); dbms_output.put_line ('| Test 1: General test.'); dbms_output.put_line ('| Pass : ''start &' || 'amp; middle &' || 'amp; end'''); dbms_output.put_line ('| Expected result: Returns ''start ''&'' middle ''&'' end''.'); dbms_output.put_line('|'); l_string := get_html.replace_scec('start &' || 'amp; middle &' || 'amp; end'); dbms_output.put_line('| Result: returned = [' || l_string || ']'); dbms_output.put_line('|'); dbms_output.put_line('| +----------+'); IF l_string = 'start &' || ' middle &' || ' end' 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 get_scec 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('-'); dbms_output.put_line('- Clean up STA...'); DELETE FROM parsed_html WHERE raw_run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name = 'GOOGLE'); DELETE FROM raw_html WHERE run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name = 'GOOGLE'); COMMIT; dbms_output.put_line('- Done.'); dbms_output.put_line('-'); END; / CONNECT ctl/ SET SERVEROUTPUT ON SIZE 1000000; BEGIN dbms_output.put_line('-'); dbms_output.put_line('- Clean up CTL...'); END; / DELETE FROM html_parse_passes WHERE raw_run_no IN (SELECT run_no FROM html_page_access WHERE html_page_name = 'GOOGLE'); DELETE FROM html_page_access WHERE html_page_name = 'GOOGLE' / DELETE FROM html_pages WHERE name = 'GOOGLE' / COMMIT / BEGIN dbms_output.put_line('- Done.'); dbms_output.put_line('-'); END; / CONNECT sta/ SET SERVEROUTPUT ON SIZE 1000000;