How to import a CSV file using PL/SQL?

In PL/SQL, external tables can be used to import CSV files. These external tables serve as a virtual representation of the data, as they act as pointers to the CSV files rather than containing the data itself.

Here is an example of importing a CSV file using an external table:

1. Set up a directory to store CSV files. For example:

CREATE DIRECTORY csv_dir AS '/path/to/csv/files';

1. Create an external table pointing to a CSV file, for example:

CREATE TABLE csv_data
(
  column1 VARCHAR2(100),
  column2 NUMBER,
  column3 DATE
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY csv_dir
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('data.csv')
)
REJECT LIMIT UNLIMITED;

In the above example, csv_data is the name of the external table, and column1, column2, and column3 are the column names in the CSV file. Each row in the CSV file will be parsed as a record in the external table.

1. Query data from an external table using the SELECT statement. For example:

SELECT * FROM csv_data;

Once the above steps are completed, you can access the data in the CSV file by querying the external table. It is important to note that the external table is just a virtual table and does not store actual data, but rather reads data by pointing to the CSV file.