Final PL/SQL Semester 2 Project: Traveler Assistance

Traveler_Assistance.sql

-- Required tables:
SELECT * FROM COUNTRIES;
SELECT * FROM REGIONS;
SELECT * FROM CURRENCIES;
SELECT * FROM SPOKEN_LANGUAGES;
SELECT * FROM LANGUAGES;

--------------PACKAGE 1: TRAVELER ADMIN PACKAGE--------------
CREATE OR REPLACE PACKAGE traveler_assistance_package AS
    TYPE country_type IS RECORD(
        country_name COUNTRIES.country_name%TYPE,
        region REGIONS.region_name%TYPE,
        currency CURRENCIES.currency_name%TYPE
    );
    TYPE countries_type IS TABLE OF country_type INDEX BY PLS_INTEGER;
    TYPE country_language_type IS RECORD(
        country_name COUNTRIES.country_name%TYPE,
        language_name LANGUAGES.language_name%TYPE,
        official_language SPOKEN_LANGUAGES.official%TYPE
    );
    TYPE country_languages_type IS TABLE OF country_language_type INDEX BY PLS_INTEGER;
    --P1
    PROCEDURE country_demographics(v_country_name VARCHAR2);
    --P2
    PROCEDURE find_region_and_currency(v_country_name IN VARCHAR2, country OUT country_type);
    -- --P3
    PROCEDURE countries_in_same_region(v_region_name IN VARCHAR2, countries OUT countries_type);
    -- --P4
    PROCEDURE print_region_array(countries countries_type);
    -- --P5
    PROCEDURE country_languages(v_country_name IN VARCHAR2, country_lang OUT country_languages_type );
    -- --P6
    PROCEDURE print_language_array(country_langs country_languages_type);
END;

CREATE OR REPLACE PACKAGE BODY traveler_assistance_package AS
    --P1
    PROCEDURE country_demographics(v_country_name VARCHAR2) IS
    BEGIN
        FOR country IN (SELECT * FROM COUNTRIES WHERE LOWER(COUNTRY_NAME) = LOWER(v_country_name)) LOOP
            DBMS_OUTPUT.PUT_LINE(country.COUNTRY_NAME || ',  ' || country.LOCATION || ', ' || country.POPULATION
                                 || ', ' || country.AIRPORTS || ', ' || country.CLIMATE);
        END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_country_name);
    END;
    --P2
    PROCEDURE find_region_and_currency(v_country_name IN VARCHAR2, country OUT country_type) IS
    BEGIN
        SELECT c.country_name, r.region_name, cu.currency_name INTO country
        FROM COUNTRIES c, REGIONS r, CURRENCIES cu
        WHERE LOWER(c.COUNTRY_NAME) = LOWER(v_country_name) AND
                c.REGION_ID = r.REGION_ID AND
                c.CURRENCY_CODE = cu.CURRENCY_CODE;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_country_name);
    END;
    --P3
    PROCEDURE countries_in_same_region(v_region_name IN VARCHAR2, countries OUT countries_type) IS
        CURSOR countries_region IS SELECT c.country_name, r.region_name, cu.currency_name
            FROM countries c, regions r, currencies cu
            WHERE c.region_id = r.region_id 
            AND c.currency_code = cu.currency_code
            AND LOWER(r.region_name) = LOWER(v_region_name);
        i PLS_INTEGER := 1;
    BEGIN
        FOR country IN countries_region LOOP
            countries(i) := country;
            i := i + 1;
        END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_region_name);
    END;
    --P4
    PROCEDURE print_region_array(countries countries_type) IS
    BEGIN
        FOR i IN countries.FIRST .. countries.LAST LOOP
            DBMS_OUTPUT.PUT_LINE(countries(i).country_name || ', ' || countries(i).region || ', ' || countries(i).currency);
        END LOOP;
    END;
    --P5
    PROCEDURE country_languages(v_country_name IN VARCHAR2, country_lang OUT country_languages_type ) IS
        CURSOR country_languages_cursor IS SELECT c.country_name, l.language_name, sl.official
            FROM COUNTRIES c,LANGUAGES l, SPOKEN_LANGUAGES sl
            WHERE LOWER(c.country_name) = LOWER(v_country_name)
                AND c.country_id = sl.country_id
                AND sl.language_id = l.language_id;
        i PLS_INTEGER := 1;
    BEGIN
        FOR country_language IN country_languages_cursor LOOP
            country_lang(i) := country_language;
            i := i + 1;
        END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'No data found for country ' || v_country_name);
    END;
    --P6
    PROCEDURE print_language_array(country_langs country_languages_type) IS
    BEGIN
        FOR i IN country_langs.FIRST .. country_langs.LAST LOOP
            DBMS_OUTPUT.PUT_LINE(country_langs(i).country_name || ', ' || country_langs(i).language_name || ', ' || country_langs(i).official_language);
        END LOOP;
    END;
END;

----------TESTS----------
------P1------
--- Select of belize
SELECT * FROM countries WHERE country_name = 'Belize';
--- Procedure of belize
BEGIN
    TRAVELER_ASSISTANCE_PACKAGE.COUNTRY_DEMOGRAPHICS('Belize');
END;
------P2------
DECLARE
    country_name VARCHAR2(50) := 'Belize';
    country TRAVELER_ASSISTANCE_PACKAGE.country_type;
BEGIN
    TRAVELER_ASSISTANCE_PACKAGE.FIND_REGION_AND_CURRENCY(country_name, country);
    DBMS_OUTPUT.PUT_LINE(country.country_name || ', ' || country.region || ', ' || country.currency);
END;
------P3 & P4------
DECLARE
    region_name VARCHAR2(50) := 'Central America';
    countries TRAVELER_ASSISTANCE_PACKAGE.countries_type;
BEGIN
    TRAVELER_ASSISTANCE_PACKAGE.COUNTRIES_IN_SAME_REGION(region_name, countries);
    DBMS_OUTPUT.PUT_LINE('Countries in the same region are: ');
    TRAVELER_ASSISTANCE_PACKAGE.PRINT_REGION_ARRAY(countries);
END;
------P5 & P6------
DECLARE
    country_name VARCHAR2(50) := 'Belize';
    country_langs TRAVELER_ASSISTANCE_PACKAGE.country_languages_type;
BEGIN
    TRAVELER_ASSISTANCE_PACKAGE.COUNTRY_LANGUAGES(country_name, country_langs);
    DBMS_OUTPUT.PUT_LINE('Languages spoken in ' || country_name || ' are: ');
    TRAVELER_ASSISTANCE_PACKAGE.PRINT_LANGUAGE_ARRAY(country_langs);
END;


--------------PACKAGE 2: TRAVELER ADMIN PACKAGE--------------
CREATE OR REPLACE PACKAGE traveler_admin_package AS
    TYPE object_rec IS RECORD(
        name USER_DEPENDENCIES.name%TYPE,
        type USER_DEPENDENCIES.type%TYPE,
        referenced_name USER_DEPENDENCIES.referenced_name%TYPE,
        referenced_type USER_DEPENDENCIES.referenced_type%TYPE
    );
    TYPE object_array IS TABLE OF object_rec INDEX BY PLS_INTEGER;
    --P1
    PROCEDURE display_disabled_triggers;
    --P2
    FUNCTION all_dependent_objects(object_name VARCHAR2) RETURN object_array;
    --P3
    PROCEDURE print_dependent_objects(objects IN object_array);
END;

CREATE OR REPLACE PACKAGE BODY traveler_admin_package AS
    --P1
    PROCEDURE display_disabled_triggers IS
    CURSOR triggers IS SELECT trigger_name FROM user_triggers WHERE status = 'DISABLED';
    BEGIN
        FOR trigger IN triggers LOOP
            DBMS_OUTPUT.PUT_LINE( 'Trigger ' || trigger.trigger_name ||' is disabled' );
        END LOOP;
    END;
    --P2
    FUNCTION all_dependent_objects(object_name VARCHAR2)
    RETURN object_array IS
        CURSOR object_cur IS SELECT name, type, referenced_name, referenced_type
                        FROM USER_DEPENDENCIES WHERE referenced_name = UPPER( object_name );
        v_objects object_array;
        i PLS_INTEGER := 1;
    BEGIN
        FOR v_object IN object_cur LOOP
            v_objects(i) := v_object;
            i := i + 1;
        END LOOP;
        IF (v_objects.COUNT < 1) THEN
            RAISE NO_DATA_FOUND;
        END IF;
        RETURN v_objects;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'No data found');
    END;
    --P3
    PROCEDURE print_dependent_objects(objects IN object_array) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('NAME          TYPE        REFERENCED_NAME         REFERENCED_TYPE');
        FOR i IN objects.FIRST .. objects.LAST LOOP
            DBMS_OUTPUT.PUT_LINE( RPAD(objects(i).name, 31) || RPAD(objects(i).type, 31) ||
                                  RPAD(objects(i).referenced_name, 31) || RPAD(objects(i).referenced_type,31) );
        END LOOP;
    END;
END;

----------TESTS----------
------P1------
--- See output of user_triggers
SELECT trigger_name FROM user_triggers;
--- DISABLE trigger
ALTER TRIGGER COUNT_TRIGGER DISABLE;
--- SHOW result
BEGIN
    traveler_admin_package.display_disabled_triggers();
END;
--- ENABLE trigger
ALTER TRIGGER COUNT_TRIGGER ENABLE;
--- SHOW result          TYPE        REFERENCED_NAME         REFERENCED_TYPE
EMP_DETAILS_VIEW               VIEW                           REGIONS
TABLE
TRAVELER_ASSISTANCE_PACKAGE    PACKAGE                        REGIONS
TABLE
TRAVELER_ASSISTANCE_PACKAGE    PACKAGE BODY                   REGIONS
TABLE

BEGIN
    traveler_admin_package.display_disabled_triggers();
END;

------P2 & P3------
--- See user dependecies
SELECT * FROM USER_DEPENDENCIES WHERE referenced_name = 'REGIONS';
--- See output of all_dependent_objects
DECLARE
    v_objects TRAVELER_ADMIN_PACKAGE.object_array;
BEGIN
    v_objects := traveler_admin_package.all_dependent_objects('regions');
    traveler_admin_package.print_dependent_objects(v_objects);
END;