Avatar of GurcanK
GurcanK
 asked on

How to Solve Auto Insert Date-Time Problem with SQLLOADER?

Dear Experts,

I have a table like below:

CREATE TABLE TA
   (      X DATE,
      Y VARCHAR2(255 BYTE),
      Z VARCHAR2(31 BYTE),
      T VARCHAR2(127 BYTE),
      K VARCHAR2(127 BYTE),
      L VARCHAR2(127 BYTE),
      M CLOB,
      N DATE DEFAULT SYSDATE
   )

N is going to be filled for each record while insertion. (There is no corresponding N field in the input file.)

Control File is like this:

LOAD DATA  ....
INFILE   ....
BADFILE  ....
DISCARDFILE ....
APPEND INTO TABLE TA
Fields terminated by "|"
TRAILING NULLCOLS
(
X DATE "MON DD HH24:MI:SS" NULLIF (X="NULL"),Y,Z,T,K,L,M,N DATE "MON DD HH24:MI:SS" NULLIF (INSERT_DATETIME="NULL")
)

Each record is being properly recorded to Table, however the field N (field for auto-insert date-time) comes always NULL. What can it be?

BR
Oracle Database

Avatar of undefined
Last Comment
Mark Geerlings

8/22/2022 - Mon
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
GurcanK

ASKER
Dear Moderator,

I got e-mail but here I could not see any expert reply?!
GurcanK

ASKER
Yes, but the requester group did not want external tables :( No way?
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
johnsone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes