Untitled

-- TravelPackages Table Creation
CREATE OR REPLACE PROCEDURE generate_dummy_travel_packages AS
    -- Arrays for package names, descriptions, and other data
    TYPE name_array IS TABLE OF VARCHAR2(255);
    package_names name_array := name_array(
        'Adventure Escape', 'Luxury Retreat', 'Family Fun', 'Romantic Getaway', 'Cultural Journey',
        'Beach Paradise', 'Mountain Adventure', 'City Explorer', 'Wildlife Safari', 'Historical Tour',
        'Foodie Delight', 'Spa and Wellness', 'Cruise Vacation', 'Winter Wonderland', 'Island Hopper',
        'Beach Getaway', 'Mountain Adventure', 'City Tour', 'Safari Expedition', 'Cruise Vacation',
        'Cultural Immersion', 'Island Escape', 'Historical Tour', 'Wildlife Encounter', 'Luxury Vacation',
        'Culinary Experience', 'Wellness Retreat', 'Family Vacation', 'Romantic Escape', 'Adventure Trip',
        'Nature Retreat', 'Culinary Adventure', 'Artistic Journey', 'Spiritual Journey', 'Eco-Friendly Tour',
        'Ski Resort', 'Desert Adventure', 'Island Adventure', 'Road Trip', 'Camping Trip',
        'Fishing Trip', 'Diving Experience', 'Surfing Trip', 'Golf Vacation', 'Photography Expedition',
        'Cultural Experience', 'Island Retreat', 'Historical Journey', 'Wildlife Safari', 'Luxury Escape', 
        'Gastronomic Tour', 'Wellness Retreat', 'Family Fun', 'Romantic Getaway', 'Adventure Sports',
        'Nature Exploration', 'Culinary Journey', 'Art and Culture', 'Spiritual Retreat', 'Eco-Tourism',
        'Skiing Trip', 'Desert Safari', 'Island Hopping', 'Road Trip', 'Camping Adventure',
        'Fishing Expedition', 'Scuba Diving', 'Surfing Adventure', 'Golf Retreat', 'Photography Tour',
        'Wine Tasting', 'Historical Exploration', 'Cultural Immersion', 'Luxury Spa Retreat', 'Yoga Retreat',
        'Cycling Tour', 'Hiking Adventure', 'Wildlife Photography', 'Cultural Festival', 'Adventure Cruise',
        'Cultural Heritage', 'Culinary Delights', 'Nature Retreat', 'Adventure Island', 'Luxury Train Journey'
    );
    descriptions name_array := name_array(
        'An unforgettable adventure experience.',
        'Relax in luxury and style.',
        'Perfect for families with kids.',
        'A romantic escape for couples.',
        'Explore the rich cultural heritage.',
        'Enjoy the sun, sand, and sea.',
        'Thrilling mountain activities await.',
        'Discover the charm of urban life.',
        'Get up close with exotic wildlife.',
        'Step back in time with historical sites.',
        'Savor the best culinary experiences.',
        'Rejuvenate with spa and wellness treatments.',
        'Sail away on a luxurious cruise.',
        'Experience the magic of winter.',
        'Hop between beautiful islands.',
         'Relax on the beach for a week', 
         'Explore the mountains for 5 days', 
         'Discover the city highlights in 3 days',
        'Experience the thrill of a safari',
         'Enjoy a week-long cruise', 
         'Immerse in local culture and traditions',
        'Unwind on a tropical island', 
        'Travel through historical sites', 
        'Witness wildlife in their natural habitat',
        'Indulge in a luxury vacation', '
        Savor local cuisine on a culinary tour', '
        Rejuvenate at a wellness retreat',
        'Experience the wilderness on a safari', 
        'Enjoy a luxurious cruise for 7 days',
        'Immerse yourself in local culture', '
        Unwind on a private island',
         'Travel through historical landmarks',
        'Witness exotic wildlife in their natural habitat', 'Indulge in a luxury vacation experience',
        'Savor the local cuisine on a gastronomic tour', 'Rejuvenate at a wellness retreat', 'Fun-filled family vacation',
        'Romantic getaway for couples', 'Thrilling adventure sports experience', 'Explore nature at its best',
        'Embark on a culinary journey', 'Discover art and culture', 'Find peace at a spiritual retreat',
        'Experience eco-tourism at its finest', 'Skiing trip in the mountains', 'Adventure through the desert',
        'Island hopping adventure', 'Road trip through scenic routes', 'Camping adventure in the wild'
    );

    -- Variables
    v_package_name VARCHAR2(255);
    v_description VARCHAR2(255);
    v_price DECIMAL(10, 2);
    v_start_date DATE;
    v_end_date DATE;
    v_max_participants INT;
    v_batch_size INT := 1000;

    -- Collections for bulk insert
    TYPE t_package_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_package_names IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
    TYPE t_descriptions IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
    TYPE t_prices IS TABLE OF DECIMAL(10, 2) INDEX BY BINARY_INTEGER;
    TYPE t_start_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;
    TYPE t_end_dates IS TABLE OF DATE INDEX BY BINARY_INTEGER;
    TYPE t_max_participants IS TABLE OF INT INDEX BY BINARY_INTEGER;

    l_package_ids t_package_ids;
    l_package_names t_package_names;
    l_descriptions t_descriptions;
    l_prices t_prices;
    l_start_dates t_start_dates;
    l_end_dates t_end_dates;
    l_max_participants t_max_participants;

BEGIN
    -- Loop to create 1,000,000 travel packages
    FOR batch IN 1..1000 LOOP
        -- Generate v_batch_size records
        FOR i IN 1..v_batch_size LOOP
            -- Generate random data
            v_package_name := package_names(TRUNC(DBMS_RANDOM.VALUE(1, package_names.COUNT)));
            v_description := descriptions(TRUNC(DBMS_RANDOM.VALUE(1, descriptions.COUNT)));
            v_price := TRUNC(DBMS_RANDOM.VALUE(100, 10000), 2);
            v_start_date := TRUNC(SYSDATE) + TRUNC(DBMS_RANDOM.VALUE(1, 365));
            v_end_date := v_start_date + TRUNC(DBMS_RANDOM.VALUE(3, 15));
            v_max_participants := TRUNC(DBMS_RANDOM.VALUE(10, 100));

            -- Add to collections
            l_package_ids(i) := package_id_seq.NEXTVAL;
            l_package_names(i) := v_package_name;
            l_descriptions(i) := v_description;
            l_prices(i) := v_price;
            l_start_dates(i) := v_start_date;
            l_end_dates(i) := v_end_date;
            l_max_participants(i) := v_max_participants;
        END LOOP;

        -- Bulk insert data
        FORALL i IN l_package_ids.FIRST..l_package_ids.LAST
            INSERT INTO TravelPackages (
                PackageID,
                PackageName,
                Description,
                Price,
                StartDate,
                EndDate,
                MaxParticipants
            ) VALUES (
                l_package_ids(i),
                l_package_names(i),
                l_descriptions(i),
                l_prices(i),
                l_start_dates(i),
                l_end_dates(i),
                l_max_participants(i)
            );

        -- Commit each batch
        /*COMMIT;
        */
        -- Progress report
        DBMS_OUTPUT.PUT_LINE('Completed batch ' || batch || ' of 1000 (' || (batch * v_batch_size) || ' records)');
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Successfully generated 1,000,000 travel package records.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END generate_dummy_travel_packages;
/
-- Enable timing in SQL*Plus
SET TIMING ON;

-- Start timing the execution
DECLARE
    start_time NUMBER;
    end_time NUMBER;
    elapsed_time NUMBER;
BEGIN
    -- Record the start time
    start_time := DBMS_UTILITY.GET_TIME;

    -- Execute the procedure
    generate_dummy_travel_packages();

    -- Record the end time
    end_time := DBMS_UTILITY.GET_TIME;

    -- Calculate elapsed time in seconds
    elapsed_time := (end_time - start_time) / 100;
    DBMS_OUTPUT.PUT_LINE('Execution Time: ' || elapsed_time || ' seconds');
END;
/

-- Disable timing after execution
/*SET TIMING OFF;

-- Execute the procedure
BEGIN
    generate_dummy_travel_packages();
END;
/
*/