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