CSV UPLOAD FROM EXTERNAL TABLE TO ORACLE TABLE

CSV UPLOAD FROM EXTERNAL TABLE TO ORACLE TABLE

Create Page
Create  2 Page Item
like: P773_TEMPLATE_FILE and P773_FILE_NAME
create dynamic action on  P773_TEMPLATE_FILE

on change item execute javascript

//Extract filename from full path
var filename = $v('P773_TEMPLATE_FILE').split('\\').pop().split('/').pop();
//Copy file name

$s("P773_FILE_NAME",filename);

Create page Process
change item value and Procedure name

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');
   EXTERNAL_TO_ORACLE_TABLE  (f_name);
 -- EXECUTE IMMEDIATE 'ALTER TABLE RAKSHA_HOSP_DETAIL_EXT LOCATION ('''||f_name||''')';
   delete from apex_application_temp_files where name = :P13_TEMPLATE_FILE;
  end if;
end;

===================================================

DROP TABLE ADMIN.DIR_DATA_UPLOAD CASCADE CONSTRAINTS;

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 OR REPLACE TRIGGER ADMIN."DIR_DATA_UPLOAD_T1"
BEFORE
insert or update or delete on "DIR_DATA_UPLOAD"
for each row
begin
IF INSERTING THEN
:NEW.ID:=DIR_ID_SEQ.NEXTVAL;
:new.CREATED_ON:= sysdate;
:new.CREATED_BY:= v('APP_USER');
:new.UPDATED_ON:= sysdate;
:new.UPDATED_BY:= v('APP_USER');
elsif updating then
:new.UPDATED_ON:= sysdate;
:new.UPDATED_BY:= v('APP_USER');
END IF;
end;
/

=======================================================

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 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 ;

===============================

DROP TABLE ADMIN.EMP_EXTARNAL CASCADE CONSTRAINTS;
CREATE TABLE ADMIN.EMP_EXTARNAL
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2),
  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 (APEX_TEMP:'25NOV19230338_EMP.csv')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

======================================