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

Table Of Contents

How to CSV UPLOAD FROM EXTERNAL TABLE TO ORACLE TABLE?

Preview

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 table is created, we just need to execute following pl/sql block to load the CSV file into the oracle table for this we need to follow these simple steps.

1. Create Page.
2. Create 2 Page Item,like : P13_TEMPLATE_FILE and P13_FILE_NAME.
3. Create on change dynamic Action on P13_TEMPLATE_FILE to execute javascript. Preview

  • Extract filename from full path and Copy file name item.:
var filename = $v('P13_TEMPLATE_FILE').split('\\').pop().split('/').pop();
$s("P13_FILE_NAME",filename);

Preview

  • Create an oracle table to store csv data to base table.
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 first process to write csv file into directory.
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 second procedure to insert data from external table to oracle table.
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 page process for calling those process which declared above.
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;

comments powered by Disqus