Load CSV From Directory using External table to Oracle Table Oracle/PLSQLs

How to CSV UPLOAD FROM EXTERNAL TABLE TO ORACLE TABLE?

To Create an External Table from CSV File, Follow these simple Steps

Create a Directory
Grant Read/Write Permission to that Directory
Place your CSV file in that directory at OS Level
Create EXTERNAL Table

Example:

Create or replace directory APEX_TEMP as ‘/home/oracle/apex_temp’;
Note: /home/oracle/apex_temp has to be physical location on disk.
grant read, write on directory APEX_TEMP to scott(user);
Put your csv file on /home/oracle/apex_temp at OS level
create table EMP_EXTARNAL

CREATE TABLE "EMP_EXTARNAL"
 ( "EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10),
 "JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2),
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0),
 "END_DATE" TIMESTAMP (6)
 )
 ORGANIZATION EXTERNAL
 ( TYPE oracle_loader
 DEFAULT DIRECTORY "APEX_TEMP"
 ACCESS PARAMETERS
 ( RECORDS DELIMITED BY NEWLINE
SKIP 1
 FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LRTRIM
 MISSING FIELD VALUES ARE NULL
 )
 LOCATION
 ( '25NOV19234458_EMP.csv'
 )
 )
 REJECT LIMIT UNLIMITED
/

Once the table is created, we just need to execute the following PL/SQL block to load the CSV file into the oracle table for this we need to follow these simple steps.

1. Create a Page.
2. Create 2 Page Items, like P13_TEMPLATE_FILE and P13_FILE_NAME.
3. Create on change dynamic Action on P13_TEMPLATE_FILE to execute JavaScript.

Extract the filename from the full path and Copy the file name item.:

var filename = $v('P13_TEMPLATE_FILE').split('\\').pop().split('/').pop();
$s("P13_FILE_NAME",filename);

Create an oracle table to store csv data to base table.

Copy Code

CREATE TABLE admin.dir_data_upload
(
   id                  NUMBER,
   file_name           VARCHAR2 (100 BYTE),
   file_mimetype       VARCHAR2 (100 BYTE),
   file_characterset   VARCHAR2 (100 BYTE),
   created_on          DATE,
   created_by          VARCHAR2 (100 BYTE),
   updated_on          DATE,
   updated_by          VARCHAR2 (100 BYTE),
   blob_file           BLOB,
   TYPE                VARCHAR2 (150 BYTE)
);

Create the first process to write a CSV file into a directory.

Copy Code

CREATE OR REPLACE PROCEDURE write_into_dir (p_file_id   IN VARCHAR2,
                                            p_dir       IN VARCHAR2)
IS
   l_blob            BLOB;
   l_blob_length     INTEGER;
   l_out_file        UTL_FILE.file_type;
   l_buffer          RAW (32767);
   l_chunk_size      BINARY_INTEGER := 32767;
   l_blob_position   INTEGER := 1;
   l_file_name       dir_data_upload.file_name%TYPE;
BEGIN
   -- Retrieve the BLOB for reading
   SELECT blob_file, file_name
     INTO l_blob, l_file_name
     FROM dir_data_upload
    WHERE file_name = p_file_id;

   l_blob_length := DBMS_LOB.getlength (l_blob);

   l_out_file := UTL_FILE.fopen (p_dir, l_file_name, 'wb' -- important. If ony w then extra carriage return/line brake       ,l_chunk_size
                                                         );

   -- Write the BLOB to file in chunks
   WHILE l_blob_position <= l_blob_length
   LOOP
      IF l_blob_position + l_chunk_size - 1 > l_blob_length
      THEN
         l_chunk_size := l_blob_length - l_blob_position + 1;
      END IF;

      DBMS_LOB.read (l_blob,
                     l_chunk_size,
                     l_blob_position,
                     l_buffer);

      UTL_FILE.put_raw (l_out_file, l_buffer, TRUE);

      l_blob_position := l_blob_position + l_chunk_size;
   END LOOP;

   -- Close the file handle
   UTL_FILE.fclose (l_out_file);
END write_into_dir;

Create a second procedure to insert data from the external table into the oracle table.

Copy Code

CREATE OR replace PROCEDURE External_to_oracle_table (p_filename IN VARCHAR2)
AS
  CURSOR h_cur IS
    SELECT *
    FROM   emp_extarnal;
  TYPE fetch_array
    IS TABLE OF h_cur%ROWTYPE;
  s_array FETCH_ARRAY;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE EMP_EXTARNAL LOCATION ('''||p_filename||''')'
    ;

    OPEN h_cur;

    LOOP
        FETCH h_cur bulk collect INTO s_array;

        FOR i IN 1..s_array.count LOOP
            INSERT INTO emp_temp
                        (empno,
                         ename,
                         job,
                         mgr,
                         hiredate,
                         sal,
                         comm,
                         deptno,
                         end_date)
            VALUES     ( S_array(i).empno,
                        S_array(i).ename,
                        S_array(i).job,
                        S_array(i).mgr,
                        S_array(i).hiredate,
                        S_array(i).sal,
                        S_array(i).comm,
                        S_array(i).deptno,
                        S_array(i).end_date );

            COMMIT;
        END LOOP;

        EXIT WHEN h_cur%NOTFOUND;
    END LOOP;

    CLOSE h_cur;
END external_to_oracle_table;

Create a page process for calling those processes declared above.

Copy Code

DECLARE
    l_attachment_id dir_data_upload.id%TYPE;
    l_attachment    dir_data_upload.blob_file%TYPE;
    l_filename      dir_data_upload.file_name%TYPE;
    l_mimetype      dir_data_upload.file_mimetype%TYPE;
    l_charset       dir_data_upload.file_characterset%TYPE;
    f_name          VARCHAR2(400);
BEGIN
    BEGIN
        SELECT f.blob_content,
               f.filename,
               f.mime_type
        INTO   l_attachment, l_filename, l_mimetype
        FROM   apex_application_temp_files f
        WHERE  f.name = :P13_TEMPLATE_FILE;
    EXCEPTION
        WHEN no_data_found THEN
          l_attachment := NULL;
    END;

    IF l_attachment IS NOT NULL THEN
      f_name := To_char(SYSDATE, 'DDMONYYHH24MISS')
                ||'_'
                ||:P13_FILE_NAME;

      INSERT INTO dir_data_upload
                  (file_name,
                   blob_file,
                   file_mimetype,
                   file_characterset,
                   TYPE)
      VALUES      ( f_name,
                   l_attachment,
                   l_mimetype,
                   l_charset,
                   'UPLOAD iNTO DIR' )
      returning id INTO l_attachment_id;

      Write_into_dir(f_name, 'APEX_TEMP'); ---FIRST PROCESS

      External_to_oracle_table (f_name); -- SECOND PROCESS

      DELETE FROM apex_application_temp_files
      WHERE  name = :P13_TEMPLATE_FILE;
    END IF;
END;