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

            How to CSV UPLOAD FROM EXTERNAL TABLE TO ORACLE TABLE?

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

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

Example:

1.  Create or replace directory APEX_TEMP as '/home/oracle/apex_temp';
2.   Note: /home/oracle/apex_temp has to be physical location on disk.
3.  grant read, write on directory APEX_TEMP to scott(user);
4.  Put your csv file on /home/oracle/apex_temp at OS level
5.  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 creaed, 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.

Extract filename from full path and Copy file name

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.
========================++++++++++++++++++++++++++++++===============================

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
=====================++++++++++++++++++++++++++++++============================

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;