Press "Enter" to skip to content

Read And Import CSV Files In Oracle PL/SQL Efficiently

The example is given below to read and import CSV file (comma delimited) in PLSQL. There is a package in Oracle forms “D2k_Delimited_String” we use to read and import comma (or any delimiter) delimited file, I have written a post also on Oracle Forms to read delimited files with this package, the following is the link https://www.foxinfotech.in/2013/02/reading-and-importing-comma-delimited.html
The same package I converted to database package to read CSV file in PLSQL efficiently, I re-named the package to “Plsql_Delimited_String”. 
The below is an example given to import csv file in PL SQL with the help of this package function Get_String, the following is the usage of this function:

Plsql_Delimited_String.Getstring ([string containing delimited text, the whole row],
                                              [occurance],
                                              [unterminated boolean default false],
                                              [delimeter]);

In the following procedure user will pass a file name which should be in the directory location created in Oracle directory object, in this example directory object ‘YOUR_DIRECTORY’ is used, change it with your directory object and copy the delimited file in that location then pass the file name to this procedure.

CREATE OR REPLACE PROCEDURE Import_Emp_File (P_FILENAME   IN     VARCHAR2,
                                             o_msg           OUT VARCHAR2)
IS
   Infile                UTL_FILE.File_Type;
   Linebuf               VARCHAR2 (4000);
   V_Getstring           VARCHAR2 (100);

   — Field Values Array
   TYPE Fieldvalue IS TABLE OF VARCHAR2 (100)
                         INDEX BY BINARY_INTEGER;

   Field_Position        Fieldvalue;

   Total_Rec_Count       NUMBER := 0;
   Total_Rec_Processed   NUMBER := 0;
BEGIN
   Infile := UTL_FILE.Fopen (‘YOUR_DIRECTORY’, P_FILENAME, ‘R’);

   LOOP
      —
      UTL_FILE.Get_Line (Infile, Linebuf);
      — adding extra pipe in end of line to read all fields

      Linebuf := Linebuf || ‘|’;

      — Suppose the file is containing Six delimited strings with pipe (|)
      FOR I IN 1 .. 6
      LOOP
         Field_Position (I) :=
            Plsql_Delimited_String.Getstring (Linebuf,
                                              I,
                                              FALSE,
                                              ‘|’);
      END LOOP;

      BEGIN
         Total_Rec_Count := Total_Rec_Count + 1;

         — an example table
         INSERT INTO EMP (EMPLOYEE_NUMBER,
                          FIRST_NAME,
                          LAST_NAME,
                          DATE_OF_JOIN,
                          EMP_TYPE,
                          DATE_OF_REGN)
             VALUES (field_position (1),
                     field_position (2),
                     field_position (3),
                     field_position (4),
                     field_position (5),
                     field_position (6));

         Total_Rec_Processed := Total_Rec_Processed + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            — ignoring error during database insertion
            NULL;
      END;
   END LOOP;

   IF UTL_FILE.is_open (infile)
   THEN
      UTL_FILE.Fclose (Infile);
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      IF UTL_FILE.is_open (infile)
      THEN
         UTL_FILE.Fclose (Infile);
      END IF;

      IF total_rec_processed > 0
      THEN
         COMMIT;
      END IF;
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (infile)
      THEN
         UTL_FILE.Fclose (Infile);
      END IF;

      o_msg := SQLERRM;
END;
/

The following is the package source of PLSQL_DELIMITED_STRING used in this example, install this package in your schema. The package contains many other utility functions which can be used to read the csv file using PLSQL:

CREATE OR REPLACE PACKAGE plsql_Delimited_String IS

    function  Counter(    Source_string     in varchar2,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ‘,’) return number;
    Procedure PutString(    Source_string     in out varchar2,
                 String_to_add     in varchar2,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ‘,’);
    Procedure PutNumber(    Source_string     in out varchar2,
                 number_to_add     in number,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ‘,’);
    Procedure PutDate(    Source_string     in out varchar2,
                 Date_to_add     in date,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ‘,’);
    function  GetString(    Source_string    in varchar2,
                 Field_position     in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ‘,’) return varchar2;
    function  GetNumber(    Source_string    in varchar2,
                 Field_position in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ‘,’) return number;
    function  GetDate(    Source_string    in varchar2,
                 Field_position in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter     in varchar2 default ‘,’) return date;
    function  Locate(    Source_string    in varchar2,
                 Search_String    in varchar2,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter    in varchar2 default ‘,’) return number;
    function  Locate(    Source_string    in varchar2,
                 Search_date    in date,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter    in varchar2 default ‘,’) return number;
    function  Locate(    Source_string    in varchar2,
                 Search_number    in number,
                 UnTerminated    in Boolean  default FALSE,
                 Delimiter    in varchar2 default ‘,’) return number;
END plsql_Delimited_String;
/

CREATE OR REPLACE PACKAGE BODY plsql_Delimited_String
IS
   FUNCTION Counter (Source_string   IN VARCHAR2,
                     UnTerminated    IN BOOLEAN DEFAULT FALSE,
                     Delimiter       IN VARCHAR2 DEFAULT ‘,’)
      RETURN NUMBER
   IS
      iModifier   PLS_INTEGER := 0;
      iOldSize    PLS_INTEGER := LENGTH (Source_string);
   BEGIN
      IF Unterminated
      THEN
         iModifier := 1;
      END IF;

      RETURN (iOldSize – LENGTH (REPLACE (Source_string, Delimiter)))
             + iModifier;
   END Counter;

   PROCEDURE PutString (Source_string    IN OUT VARCHAR2,
                        String_to_add    IN     VARCHAR2,
                        Field_position   IN     NUMBER,
                        UnTerminated     IN     BOOLEAN DEFAULT FALSE,
                        Delimiter        IN     VARCHAR2 DEFAULT ‘,’)
   IS
      iStrLen       PLS_INTEGER := LENGTH (Source_String);
      iStrFragLen   PLS_INTEGER := LENGTH (String_to_add);
      iPtrStart     PLS_INTEGER := 0;
      iPtrEnd       PLS_INTEGER := 0;
      vcSepBuffer   VARCHAR2 (2000);
      iCounter      PLS_INTEGER;
   BEGIN
      — 1. is the string Zero Length?
      IF iStrLen = 0
      THEN
         IF Field_Position > 1
         THEN
            FOR iCounter IN 2 .. Field_Position
            LOOP
               vcSepBuffer := vcSepBuffer || Delimiter;
            END LOOP;
         END IF;

         Source_string := vcSepBuffer || String_to_add;

         IF NOT UnTerminated
         THEN
            Source_string := Source_String || Delimiter;
            iStrLen := LENGTH (Source_string);
         END IF;
      ELSE
         — 2. Cope with unterminated strings
         IF UnTerminated
         THEN
            Source_string := Source_string || Delimiter;
         END IF;

         — 3. Locate the nth-1 separator
         IF Field_Position > 1
         THEN
            LOOP
               iPtrStart :=
                  (INSTR (Source_string || vcSepBuffer,
                          Delimiter,
                          1,
                          Field_Position – 1)
                   – 1)
                  + LENGTH (Delimiter);
               EXIT WHEN iPtrStart > 0;
               vcSepBuffer := vcSepBuffer || Delimiter;
            END LOOP;

            IF vcSepBuffer IS NOT NULL
            THEN
               iPtrEnd := iStrLen;
            ELSE
               iPtrEnd :=
                  INSTR (Source_string,
                         Delimiter,
                         1,
                         Field_Position);

               IF iPtrEnd = 0
               THEN
                  iPtrEnd := iStrLen;
               END IF;
            END IF;
         ELSE
            iPtrStart := 0;
            iPtrEnd :=
               INSTR (Source_string,
                      Delimiter,
                      1,
                      Field_Position);

            IF iPtrEnd = 0
            THEN
               iPtrEnd := iStrLen;
            END IF;
         END IF;

         — 3. Rebuild the string
         Source_string :=
               SUBSTR (Source_string, 1, iPtrStart)
            || vcSepBuffer
            || String_to_add
            || Delimiter
            || SUBSTR (Source_string, iPtrEnd + LENGTH (Delimiter));

         — 4. Sort out termination
         IF UnTerminated
         THEN
            Source_string :=
               SUBSTR (Source_String,
                       1,
                       (LENGTH (Source_string) – LENGTH (Delimiter)));
         END IF;
      END IF;
   END PutString;

   PROCEDURE PutNumber (Source_string    IN OUT VARCHAR2,
                        number_to_add    IN     NUMBER,
                        Field_position   IN     NUMBER,
                        UnTerminated     IN     BOOLEAN DEFAULT FALSE,
                        Delimiter        IN     VARCHAR2 DEFAULT ‘,’)
   IS
   BEGIN
      — 1. Just do datatype conversions and call the varchar2 varsion of put..
      plsql_Delimited_String.PutString (Source_string,
                                      TO_CHAR (number_to_add),
                                      Field_position,
                                      UnTerminated,
                                      Delimiter);
   END PutNumber;

   PROCEDURE PutDate (Source_string    IN OUT VARCHAR2,
                      Date_to_add      IN     DATE,
                      Field_position   IN     NUMBER,
                      UnTerminated     IN     BOOLEAN DEFAULT FALSE,
                      Delimiter        IN     VARCHAR2 DEFAULT ‘,’)
   IS
   BEGIN
      — 1. Just do datatype conversions and call the varchar2 varsion of put..
      plsql_Delimited_String.
       PutString (Source_string,
                  TO_CHAR (date_to_add, ‘DD-MON-YYYY HH24:MI:SS’),
                  Field_position,
                  UnTerminated,
                  Delimiter);
   END PutDate;

   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 (2000) := 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;                                         /* String Version */

   FUNCTION GetNumber (Source_string    IN VARCHAR2,
                       Field_position   IN NUMBER,
                       UnTerminated     IN BOOLEAN DEFAULT FALSE,
                       Delimiter        IN VARCHAR2 DEFAULT ‘,’)
      RETURN NUMBER
   IS
      iRc   PLS_INTEGER;
   BEGIN
      RETURN TO_NUMBER (GetString (Source_string,
                                   Field_Position,
                                   UnTerminated,
                                   Delimiter));
   END GetNumber;                                         /* Number Version */

   FUNCTION GetDate (Source_string    IN VARCHAR2,
                     Field_position   IN NUMBER,
                     UnTerminated     IN BOOLEAN DEFAULT FALSE,
                     Delimiter        IN VARCHAR2 DEFAULT ‘,’)
      RETURN DATE
   IS
   BEGIN
      RETURN TO_DATE (GetString (Source_string,
                                 Field_Position,
                                 UnTerminated,
                                 Delimiter), ‘DD-MON-YYYY HH24:MI:SS’);
   END GetDate;                                             /* Date Version */

   FUNCTION Locate (Source_string   IN VARCHAR2,
                    Search_String   IN VARCHAR2,
                    UnTerminated    IN BOOLEAN DEFAULT FALSE,
                    Delimiter       IN VARCHAR2 DEFAULT ‘,’)
      RETURN NUMBER
   IS
      iHit        PLS_INTEGER := 0;
      iCounter    PLS_INTEGER;
      vcCompare   VARCHAR2 (2000);
   BEGIN
      FOR iCounter IN 1 .. Counter (Source_String, UnTerminated, Delimiter)
      LOOP
         IF GetString (Source_String,
                       iCounter,
                       UnTerminated,
                       Delimiter) = Search_String
         THEN
            iHit := iCounter;
            EXIT;
         END IF;
      END LOOP;

      RETURN iHit;
   END Locate;

   FUNCTION Locate (Source_string   IN VARCHAR2,
                    Search_date     IN DATE,
                    UnTerminated    IN BOOLEAN DEFAULT FALSE,
                    Delimiter       IN VARCHAR2 DEFAULT ‘,’)
      RETURN NUMBER
   IS
   BEGIN
      RETURN Locate (Source_string,
                     TO_CHAR (Search_date, ‘DD-MON-YYYY HH24:MI:SS’),
                     UnTerminated,
                     Delimiter);
   END Locate;                                              /* Date Version */

   FUNCTION Locate (Source_string   IN VARCHAR2,
                    Search_number   IN NUMBER,
                    UnTerminated    IN BOOLEAN DEFAULT FALSE,
                    Delimiter       IN VARCHAR2 DEFAULT ‘,’)
      RETURN NUMBER
   IS
   BEGIN
      RETURN Locate (Source_string,
                     TO_CHAR (Search_number),
                     UnTerminated,
                     Delimiter);
   END Locate;                                            /* Number Version */
END;                                                                /* Body */
/