Home How do I populate null for column missing for external tables | Oracle | PLSQL

Questions

How do I populate null for column missing for external tables | Oracle | PLSQL

April 28, 2023

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.

>Solution :

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>