Home » RDBMS Server » Server Utilities » SQL Loader Problem.
SQL Loader Problem. [message #158111] Wed, 08 February 2006 09:29 Go to next message
Ganeshbabu Venkat
Messages: 17
Registered: March 2001
Junior Member
I'm trying to load a bunch of records using SQL Loader. One of the fields need to have a SYSTIMESTAMP value in to a varchar for some reason SQL Loader is not loading the SYSTIMESTAMP value it errors out.

(
ORIGINAL_LOAD_DT sysdate,
LAST_NOTIFICATION_DT systimestamp,
NPA_CD POSITION(2:4),
NXX_CD POSITION(5:7),
LINE_NBR POSITION(8:11),
NOTIFICATION_SOURCE_CD POSITION(12:14)
)

Any help is appreciated.

Report message to a moderator

Re: SQL Loader Problem. [message #158113 is a reply to message #158111] Wed, 08 February 2006 09:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post the Table DDL and sample data

Report message to a moderator

Re: SQL Loader Problem. [message #158116 is a reply to message #158111] Wed, 08 February 2006 09:43 Go to previous messageGo to next message
Ganeshbabu Venkat
Messages: 17
Registered: March 2001
Junior Member
Table DDL
CREATE TABLE REFRESH_SUPPRESSION_STAGING
(
NOTIFICATION_SOURCE_CD CHAR(3 BYTE),
LINE_NBR CHAR(4 BYTE),
NOTIFICATION_DT VARCHAR2(30 BYTE) DEFAULT '9999-12-31',
EXPIRATION_DT VARCHAR2(30 BYTE) DEFAULT '9999-12-31',
UPDATED_BY_APPLICATION_USER_ID VARCHAR2(30 BYTE) DEFAULT USER,
ORIGINAL_LOAD_DT VARCHAR2(30 BYTE) DEFAULT '9999-12-31',
ACTION_CD CHAR(1 BYTE),
NPA_CD CHAR(3 BYTE),
NXX_CD CHAR(3 BYTE),
STATE_CD CHAR(2 BYTE) DEFAULT ' ',
LAST_NOTIFICATION_DT VARCHAR2(50 BYTE) DEFAULT NULL
)

Sample Data

1REFRESHSMO 20051220000111500
26182886132SMO
26182886133SIN
26182886134SOK
26182886135STX
26182886136SWI
26182886137SMO
26182886138SMO
26182886139SMO
26182886140SMO
26182886141SMO
26182886142SMO
26182886143SMO
26182886144SMO
26182886144SMO
21787656785ABC
3200512200000009500000093

But the data is independent of the systemtimestamp. i'm trying to insert to the millisecond precision.

when i do a select systimestamp from dual i get the following and when i try manually inserting in to the data it works too.. only thro SQL loader it doesnt work.

select systimestamp from dual
2/8/2006 9:28:34.425454 AM -06:00

Report message to a moderator

Re: SQL Loader Problem. [message #158129 is a reply to message #158116] Wed, 08 February 2006 10:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I have not worked with sql*loader + systimestamp datatype and i have no idea on top of myhead.
what is your oracle version?
What kind of error is that? check the logfile.
Shouldnt you be doing somekind of conversion using TO_CHAR? (because the base table is defined as varchar2).
In your table DDL, LAST_NOTIFICATION_DT is the last column.
But you are using it in second position in control file.
May be you need a FILLER function?

Report message to a moderator

Re: SQL Loader Problem. [message #158140 is a reply to message #158129] Wed, 08 February 2006 11:28 Go to previous messageGo to next message
Ganeshbabu Venkat
Messages: 17
Registered: March 2001
Junior Member
Never mind. figured it out.. we need a double quotes

"systimestamp"

Report message to a moderator

Re: SQL Loader Problem. [message #158148 is a reply to message #158140] Wed, 08 February 2006 12:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
cool.

Report message to a moderator

Re: SQL Loader Problem. [message #237720 is a reply to message #158148] Tue, 15 May 2007 09:14 Go to previous message
rahulcvijay
Messages: 4
Registered: May 2007
Junior Member
i have a similar issue.

i have a row ROW_ADDED_DATE_TIME of type TIMESTAMP(6) of a table which need to be populated with systimestamp.But when i specify ROW_ADDED_DATE_TIME "systimestamp" in the control file using double quotes as specified by a member i get an error in the log file

Record 1: Rejected - Error on table AMIE_USER.DATA_SOURCE_BARTRAW, column ROW_ADDED_DATE_TIME.
ORA-01830: date format picture ends before converting entire input string

Problem is slightly different from the above one because my datatype is timestamp and not varchar

Report message to a moderator

Previous Topic: to insert data based on the count of col's data
Next Topic: how to not load some records while loading in sqlloader
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 25 12:21:14 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.