Untitled

-- PackageDestinations Table Creation (Simulation)
CREATE OR REPLACE PROCEDURE simulate_dummy_package_destinations AS
    -- Variables
    v_batch_size INT := 100000;

    -- Collections for bulk insert
    TYPE t_package_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;
    TYPE t_destination_ids IS TABLE OF INT INDEX BY BINARY_INTEGER;

    l_package_ids t_package_ids;
    l_destination_ids t_destination_ids;

    -- Preloaded PackageIDs and DestinationIDs
    package_ids t_package_ids;
    destination_ids t_destination_ids;

    -- Timing variables
    start_time NUMBER;
    end_time NUMBER;
    elapsed_time NUMBER;

BEGIN
    -- Record the start time
    start_time := DBMS_UTILITY.GET_TIME;

    -- Preload all PackageIDs into memory
    SELECT PackageID BULK COLLECT INTO package_ids FROM TravelPackages;

    -- Preload all DestinationIDs into memory
    SELECT DestinationID BULK COLLECT INTO destination_ids FROM Destinations;

    -- Check if data exists
    IF package_ids.COUNT = 0 OR destination_ids.COUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No PackageIDs or DestinationIDs found in the respective tables.');
        RETURN;
    END IF;

    -- Loop to create 100,000 PackageDestinations
    FOR batch IN 1..100 LOOP
        -- Generate v_batch_size records
        FOR i IN 1..v_batch_size LOOP
            -- Generate random PackageID and DestinationID
            l_package_ids(i) := package_ids(TRUNC(DBMS_RANDOM.VALUE(1, package_ids.COUNT + 1)));
            l_destination_ids(i) := destination_ids(TRUNC(DBMS_RANDOM.VALUE(1, destination_ids.COUNT + 1)));
        END LOOP;

        -- Simulate progress report
        DBMS_OUTPUT.PUT_LINE('Simulated batch ' || batch || ' of 100 (' || (batch * v_batch_size) || ' records)');

        -- Commented out actual bulk insert
        /*
        FORALL i IN l_package_ids.FIRST..l_package_ids.LAST
            INSERT INTO PackageDestinations (
                PackageID,
                DestinationID
            ) VALUES (
                l_package_ids(i),
                l_destination_ids(i)
            );

        -- Commit each batch
        COMMIT;
        */
    END LOOP;

    -- 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 (Simulation): ' || elapsed_time || ' seconds');
END;
/

-- Execute the procedure
BEGIN
    simulate_dummy_package_destinations();
END;
/