A CSV file (Comma-Separated Values file) is a plain text file in which individual fields are separated by commas. This format is commonly used for exchanging data between systems because it is simple and widely supported. In this article, I will demonstrate how to import a CSV file into an Oracle table using a stored procedure.
A stored procedure in Oracle is a PL/SQL program unit that can exist either as a standalone procedure or as part of a database package. Stored procedures allow you to encapsulate complex logic and reuse it whenever needed.
Before proceeding, you must create a directory object in your Oracle database that points to the server directory where your CSV file is stored. In the following example, I use a directory object named CSV_DIR. The procedure I’ll describe is called read_csv. Additionally, within the procedure, there is a helper function called GetString, which extracts each delimited value from a line in the CSV file one by one.
Loading a CSV File into an Oracle Table Using a PL/SQL Procedure
Below is an example of a PL/SQL procedure that reads data from a CSV file and inserts it into the EMP table:
CREATE OR REPLACE PROCEDURE read_csv
IS
l_file_type UTL_FILE.file_type;
l_string VARCHAR2 (32765);
TYPE Fieldvalue IS TABLE OF VARCHAR2 (4000)
INDEX BY BINARY_INTEGER;
t_field Fieldvalue;
FUNCTION GetString (Source_string IN VARCHAR2,
Field_position IN NUMBER,
UnTerminated IN BOOLEAN DEFAULT FALSE,
Delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2
IS
iPtrEnd PLS_INTEGER := 0;
iPtrStart PLS_INTEGER := 0;
vcSourceStrCopy VARCHAR2 (4000) := Source_string;
BEGIN
IF UnTerminated
THEN
vcSourceStrCopy := vcSourceStrCopy || Delimiter;
END IF;
IF Field_Position > 1
THEN
iPtrStart :=
INSTR (vcSourceStrCopy,
Delimiter,
1,
Field_Position - 1)
+ LENGTH (Delimiter);
ELSE
iPtrStart := 1;
END IF;
iPtrEnd :=
INSTR (vcSourceStrCopy,
Delimiter,
1,
Field_Position);
RETURN SUBSTR (vcSourceStrCopy, iPtrStart, (iPtrEnd - iPtrStart));
END GetString;
BEGIN
l_file_type := UTL_FILE.Fopen ('CSV_DIR', 'abc.csv', 'r');
LOOP
UTL_FILE.Get_Line (l_file_type, l_string);
l_string := l_string || ',';
FOR n IN 1 .. REGEXP_COUNT (l_string, ',')
LOOP
t_field (n) :=
Getstring (l_string,
n,
FALSE,
',');
END LOOP;
INSERT INTO EMP (EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
VALUES (t_field (1),
t_field (2),
t_field (3),
t_field (4),
TO_DATE (t_field (5), 'dd/mm/yyyy'),
t_field (6),
t_field (7),
t_field (8));
END LOOP;
UTL_FILE.Fclose (l_file_type);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (l_file_type)
THEN
UTL_FILE.Fclose (l_file_type);
END IF;
END;Important Notes
- Directory Object:
You must ensure that the Oracle directory object (CSV_DIRin this example) points to the correct server path where your CSV file (abc.csv) is located. The Oracle database user running the procedure must also have the necessary read privileges on this directory. - Field Mapping:
It is essential to analyze your CSV file and map its columns to the target table correctly. The order and data types of the columns in the CSV should match the order and types expected by theINSERTstatement in your procedure. - Date Formats:
Pay special attention to the format of date fields in your CSV file. The above example assumes dates are in thedd/mm/yyyyformat. If your CSV uses a different date format, you must adjust theTO_DATEfunction accordingly to avoid conversion errors. - Error Handling:
The procedure includes basic exception handling to ensure the file is closed properly in case of an error. You may consider enhancing the error handling to log errors or provide more diagnostic information if needed.
By following this approach, you can efficiently load data from a CSV file into an Oracle table using a PL/SQL stored procedure. This method is particularly useful for automating data imports and integrating external data sources into your Oracle database.
