OraFAQ Forum: Server Utilities » SQL LOADER DATE FORMATING HELP

Home » RDBMS Server » Server Utilities » SQL LOADER DATE FORMATING HELP

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL LOADER DATE FORMATING HELP [message #71664]

Thu, 12 December 2002 13:11

Vidyalakshmi Iyer
Messages: 13
Registered: April 2002

Junior Member

test.ctl
----------
LOAD DATA
APPEND
INTO TABLE test_m1
(
F1 POSITION(1:14) DATE(14) "YYYYMMDDHHMMSS",
F2 POSITION(15:28) Date(14) "YYYYMMDDHHMMSS",
F3 POSITION(29:30) char,
F4 POSITION(31:35) char
)

Command used to load:
---------------------
sqlldr scott/tiger control=test.ctl log=test.log data=test.txt direct=true parallel=true

test.txt
---------
20021101090739200211040812500000000

Errors I get:
-------------
Error calling once/load initialization
ORA-02354: Conversion initialization error occurred on field F1
ORA-02351: Record 0: Rejected - Error on table TEST_M1, column F1

ORA-01810: format code appears twice

Can't guess why?

As you see the data file is correct.

The structure of the table is
as follows:
desc test_m1
Name Null? Type
----------------------- ------ ------
F1 DATE
F2 DATE
F3 VARCHAR2(2)
F4 VARCHAR2(5)

What is the problem?

Any help will be really appreciated.

Thanks a ton in advance,
Regards,
Vidya

Report message to a moderator

Re: SQL LOADER DATE FORMATING HELP [message #71665 is a reply to message #71664]

Thu, 12 December 2002 13:44

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I beleive, it is SMALL typo u have made
in the date format

-- in your code hh:mm:ss will treated as hour:month:sec
SQL> ed
Wrote file afiedt.buf

  1* select to_char(sysdate,'dd:mm:yyyy hh:mm:ss') from dual
SQL> /

TO_CHAR(SYSDATE,'DD
-------------------
12:12:2002 03:12:53

-- if you replace with hh:mi:ss this is the result
SQL> ed
Wrote file afiedt.buf

  1* select to_char(sysdate,'dd:mm:yyyy hh:mi:ss') from dual
SQL> /

TO_CHAR(SYSDATE,'DD
-------------------
12:12:2002 03:29:00

SQL>

Report message to a moderator

Re: SQL LOADER DATE FORMATING HELP [message #72128 is a reply to message #71664]

Sat, 15 March 2003 06:04

R K Singh
Messages: 1
Registered: March 2003

Junior Member

Thanks a lot ..

Report message to a moderator

Previous Topic:	problems installing 8i on pentium IV
Next Topic:	Loader with triggers (or not)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 14:25:36 CST 2025