I have been stuck at this moment not getting what I need to do
I have a file called - employee.csv with 4 columns
EMPID,EMPNAME,EMPAGE,EMPCREATION
1,ABC,12,202190211
2,XYZ,24,202190211
3,HJK,25,202190211
My employee table structure
create table employee
(
empid number,
empname varchar2(255),
empage varchar2(255),
empcreation date
emprole varchar2(255),
empgroup varchar2(255)
)
As you see my above table has 6 columns and my data file has only 4 columns
When I am using oracle external to load files my external table is empty
create table demo_employee
(
empid number,
empname varchar2(255),
empage varchar2(255),
empcreation date
emprole varchar2(255),
empgroup varchar2(255)
)organization external ( type oracle_loader default directory ABC access parameters
( fields terminated by ',') location ('employee.csv')reject limit unlimited;
My external table is created but no records in it. In case my column is missing in my file then data should be populated for it as NULL and proceed.
Did you check log file? It probably says that date format is invalid. empcreation sounds like a date, and you want to put 202190211 in there. What is it? 2021 might be a year; what's 9 that follows? Or 90?
I modified sample data so that it actually represents valid date values:
1,ABC,12,20230423 --> 23rd of April 2023
2,XYZ,24,20230427
3,HJK,25,20210903
Then, create table (for prettier output, I modified column lengths):
SQL> CREATE TABLE employee
2 (
3 empid NUMBER,
4 empname VARCHAR2 (10),
5 empage VARCHAR2 (10),
6 empcreation DATE,
7 emprole VARCHAR2 (10),
8 empgroup VARCHAR2 (10)
9 );
Table created.
External table; note missing field values are null and empcreation's datatype (varchar2, not date):
SQL> CREATE TABLE demo_employee
2 (
3 empid NUMBER,
4 empname VARCHAR2 (10),
5 empage VARCHAR2 (10),
6 empcreation VARCHAR2 (10),
7 emprole VARCHAR2 (10),
8 empgroup VARCHAR2 (10)
9 )
10 ORGANIZATION EXTERNAL
11 (
12 TYPE oracle_loader
13 DEFAULT DIRECTORY dpdir
14 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE
15 FIELDS TERMINATED BY "," MISSING FIELD VALUES ARE NULL)
16 LOCATION ('test_so.csv'))
17 REJECT LIMIT UNLIMITED;
Table created.
Is anything in there? Yes!
SQL> SELECT * FROM demo_employee;
EMPID EMPNAME EMPAGE EMPCREATIO EMPROLE EMPGROUP
---------- ---------- ---------- ---------- ---------- ----------
1 ABC 12 20230423
2 XYZ 24 20230427
3 HJK 25 20210903
Now, transfer data into the target table, providing format mask for empcreation:
SQL> INSERT INTO employee (empid,
2 empname,
3 empage,
4 empcreation)
5 SELECT empid, empname, empage, TO_DATE (empcreation, 'yyyymmdd') FROM demo_employee;
3 rows created.
Final result:
SQL> SELECT * FROM employee;
EMPID EMPNAME EMPAGE EMPCREATIO EMPROLE EMPGROUP
---------- ---------- ---------- ---------- ---------- ----------
1 ABC 12 23.04.2023
2 XYZ 24 27.04.2023
3 HJK 25 03.09.2021
SQL>