Creating a MasterTable from a CSV file using PL/SQL: A Step-by-Step Guide

Learn how to import and structure large amounts of data into a database using PL/SQL, with detailed instructions and error handling included

Importing large amounts of data into a database can be a challenging task, but with the help of PL/SQL, it can be a smooth and efficient process. In this tutorial, we'll walk you through creating a MasterTable in Oracle SQL using PL/SQL, with the data source being a CSV file. This method is proper when dealing with large amounts of data, and allows for flexibility in structuring the data to fit the database schema. We'll also include error handling and commit statements to ensure data integrity. Whether you're a beginner or an experienced user, this step-by-step guide will give you the knowledge to confidently handle data importation.

Let us dive in... For proper understanding, I will try to create it as a step-by-step tutorial.

Step 1: Create the MasterTable

This script creates a table called "MasterTable" with columns for each field in the example_data.csv file, as well as an ID column that is generated automatically.

CREATE TABLE MasterTable (
  ID NUMBER GENERATED ALWAYS AS IDENTITY,
  Country VARCHAR2(100),
  TotalCases NUMBER,
  NewCases NUMBER,
  TotalDeaths NUMBER,
  NewDeaths NUMBER,
  TotalRecovered NUMBER,
  NewRecovered NUMBER,
  ActiveCases NUMBER,
  SeriousCritical NUMBER,
  TotCasesPer1M NUMBER,
  DeathsPer1M NUMBER,
  TotalTests NUMBER,
  TestsPer1M NUMBER,
  Population NUMBER,
  Continent VARCHAR2(50),
  CONSTRAINT pk_MasterTable PRIMARY KEY (ID)
);

Step 2: Open the example_data.csv file and read the data

This code opens the example_data.csv file and reads each line, splitting the data into an array of row data using the REGEXP_REPLACE and DBMS_SQL.VARCHAR2A functions. It then loops through each element of the array, removing any commas or plus signs and setting any empty values to 0.

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
  line_text VARCHAR2(4000);
  row_data_arr DBMS_SQL.VARCHAR2A;
  row_count NUMBER := 1;
BEGIN
  file_handle := UTL_FILE.FOPEN('<directory_path>', 'example_data.csv', 'R');
  LOOP
    UTL_FILE.GET_LINE(file_handle, line_text);
    EXIT WHEN line_text IS NULL;
    row_data_arr := DBMS_SQL.VARCHAR2A(REGEXP_REPLACE(line_text, ',+$', null), ',');
    FOR i IN 1..row_data_arr.COUNT LOOP
      IF row_data_arr(i) IS NULL OR row_data_arr(i) = '' THEN
        row_data_arr(i) := '0';
      ELSE
        row_data_arr(i) := REGEXP_REPLACE(row_data_arr(i), ',|\+', '');
      END IF;
    END LOOP;
    -- insert row data into MasterTable
  END LOOP;
  UTL_FILE.FCLOSE(file_handle);
END;

Step 3: Insert the row data into the MasterTable

This code inserts the row data into the MasterTable using an INSERT INTO statement. It also increments the row count for each row inserted.

INSERT INTO MasterTable (ID, Country, TotalCases, NewCases, TotalDeaths, NewDeaths, TotalRecovered, NewRecovered, ActiveCases, SeriousCritical, TotCasesPer1M,
    DeathsPer1M, TotalTests, TestsPer1M, Population, Continent)
    VALUES (row_count, row_data_arr(2), row_data_arr(3), row_data_arr(4), row_data_arr(5), row_data_arr(6), row_data_arr(7), row_data_arr(8), row_data_arr(9), row_data_arr(10), row_data_arr(11), row_data_arr(12), row_data_arr(13), row_data_arr(14), row_data_arr(15));
    row_count := row_count + 1;

Step 4: Add indexes and constraints to the MasterTable

This code adds an index to the "Country" column for improved query performance, as well as a check constraint to ensure that the "TotalCases" column is always greater than or equal to the "TotalDeaths"

CREATE INDEX idx_Country ON MasterTable(Country);

ALTER TABLE MasterTable ADD CONSTRAINT chk_TotalCases_TotalDeaths CHECK (TotalCases >= TotalDeaths);

Step 5: Add error handling and commit

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error on row: ' || row_count || ' - ' || SQLERRM);
    CONTINUE;
END;

This code adds error handling to the PL/SQL block. If an error occurs during data insertion, it will be logged to the console output and the loop will continue to the next row.

COMMIT;

This code commits the data to the MasterTable once all rows have been inserted without error.

Step 6: Run the PL/SQL block

To run the PL/SQL block, simply copy and paste the entire code into a SQL editor and replace "<directory_path>" in Step 2 with the directory path where the example_data.csv file is located. Then execute the code by clicking the "Run" button.

The code will read each line of the example_data.csv file, insert the data into the MasterTable, and log any errors to the console output. Once the data has been successfully inserted, a commit statement will be executed to save the data to the MasterTable.

And that's it! You should now have a MasterTable containing the data from the example_data.csv file.

However, I don't use PL/SQL that much I just learned this today hence sharing it as a tech blog. If you are looking for anything related to APIs, Machine Learning, Java and Cloud check out my Blog and subscribe to my newsletter.

Thanks for Reading. Take care.