Introduction

In this article, we will explore how to insert a foreign key from a pre-generated table in Oracle SQL. The example provided uses the sys.odcinumberlist data type to store an array of values and then selects a random value from the array.

Background

The question at hand involves generating customer and place tables using a PL/SQL generator and then inserting booking records that reference both the customer ID and table number. However, since the numbers in the customer table are changing every time it is regenerated, we need to find a way to select a random value from this array.

The Problem

The challenge lies in selecting a random value from an array without having control over the actual values stored in that array. We cannot simply select any value because we need to ensure that the selected value exists in the customer table.

Solution 1: Using BULK COLLECT and SYS.ODCINUMBERLIST

The first solution provided uses the BULK COLLECT clause to store an array of values from the customer table and then selects a random value from this array. However, as we will explore later, there is a more efficient way to achieve the same result using pure SQL.

Solution 2: Pure SQL

The second solution provided uses a combination of subqueries and the ROW_NUMBER() function to select a random value from both the customer table and the place table. This approach allows us to generate booking records that reference both the customer ID and table number without having control over the actual values in the array.

Implementation

Let’s dive deeper into the pure SQL solution provided in the question:

insert into booking(booking_id,date_of_booking,number_of_persons,customer_id,table_number)
with 
  customer_subq as (
    select customer_id, row_number() over (order by customer_id) rn from customer
  ),
  place_subq as (
    select table_number, row_number() over (order by table_number) rn from place
  ),
  params as (
    select 1500 number_of_generated_records,
           (select count(1) from customer) customer_count,
           (select count(1) from place) place_count
    from   dual
  ),
  random_numbers as (
    select round(dbms_random.value(1,1000)) random_number1,
           round(dbms_random.value(1,1000)) random_number2,
           round(dbms_random.value(1,1000)) random_number3,
           round(dbms_random.value(1,1000)) random_number4
    from   dual,params
    connect by level <= number_of_generated_records
  )
  select booking_seq.nextval booking_id,
         trunc(sysdate) + mod(random_number1,365) date_of_booking,
         mod(random_number1,100) number_of_persons,
         customer_id,
         table_number
  from   random_numbers, 
         params,
         customer_subq,
         place_subq
  where  mod(random_number1,customer_count) + 1 = customer_subq.rn
  and    mod(random_number2,place_count) + 1 = place_subq.rn;

Explanation

This solution works by generating a list of random numbers between 0 and 999 for each table. The RANDOM_NUMBER1 is used to select the customer ID, and RANDOM_NUMBER2 is used to select the table number. We use the ROW_NUMBER() function to assign a unique row number to each record in both tables.

The PARAMS subquery provides the total count of records for each table. The CONNECT BY LEVEL <= NUMBER_OF_GENERATED_RECORDS clause ensures that we generate at most 1500 random numbers (defined by the NUMBER_OF_GENERATED_RECORDS parameter).

Finally, we use a WHERE clause to select only the rows where MOD(RANDOM_NUMBER1, CUSTOMER_COUNT) + 1 = CUSTOMER_SUBQ.RN and MOD(RANDOM_NUMBER2, PLACE_COUNT) + 1 = PLACE_SUBQ.RN. This ensures that we select only valid customer IDs and table numbers.

Conclusion

In conclusion, the pure SQL solution provided in this answer offers a more efficient way to insert foreign keys from pre-generated tables without relying on the BULK COLLECT clause. By using subqueries and the ROW_NUMBER() function, we can generate booking records that reference both the customer ID and table number while minimizing the impact of changing values in the array.

Last modified on 2023-09-23