OraFAQ Forum: Server Utilities » how to load date and time from text file to oracle table through sqlloader

Home » RDBMS Server » Server Utilities » how to load date and time from text file to oracle table through sqlloader

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

how to load date and time from text file to oracle table through sqlloader [message #458510]

Mon, 31 May 2010 02:27

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

hi friends

i need you to show me what i miss to load date and time from text file to oracle table through sqlloader

this is my data in this path (c:\external\my_data.txt)

7369,SMITH,17-NOV-81,09:14:04,CLERK,20
7499,ALLEN,01-MAY-81,17:06:08,SALESMAN,30
7521,WARD,09-JUN-81,17:06:30,SALESMAN,30
7566,JONES,02-APR-81,09:24:10,MANAGER,20
7654,MARTIN,28-SEP-81,17:24:10,SALESMAN,30

my table in database emp2

create table emp2 (empno number,
ename varchar2(20),
hiredate date,
etime date,
ejob varchar2(20),
deptno number);

the control file code in this path (c:\external\ctrl.ctl)

load data
infile 'C:\external\my_data.txt'
into table emp2
fields terminated by ','
(empno, ename, hiredate, etime, ejob, deptno)

this is the error :

C:\>sqlldr scott/tiger control=C:\external\ctrl.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 09:45:10 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

C:\>

any help i greatly appreciated

thanks

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458512 is a reply to message #458510]

Mon, 31 May 2010 02:35

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's no error. At least, I don't see any.

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458513 is a reply to message #458512]

Mon, 31 May 2010 02:38

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Error? Which error?

Regards
Michel

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458514 is a reply to message #458512]

Mon, 31 May 2010 02:39

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

hi Littlefoot

thanks for reply but my table emp2 it's still empty could you help me please

SQL> select * from emp2;

no rows selected

SQL>

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458516 is a reply to message #458513]

Mon, 31 May 2010 02:40

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

hi Michel Cadot

why my table it's still empty please i'm stucked

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458518 is a reply to message #458516]

Mon, 31 May 2010 02:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SQL> desc emp2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(20)
 HIREDATE                                           DATE
 ETIME                                              DATE
 EJOB                                               VARCHAR2(20)
 DEPTNO                                             NUMBER

SQL>

Control file (slightly modified - note TO_DATE function I have used. Disregard Croatian months' names in sample data):

load data
infile *
replace
into table emp2
fields terminated by ','
  (empno, 
   ename, 
   hiredate "to_date(:hiredate, 'dd-mon-yy')", 
   etime "to_date(:etime, 'hh24:mi:ss')", 
   ejob, 
   deptno
)

begindata
7369,SMITH,17-STU-81,09:14:04,CLERK,20
7499,ALLEN,01-SVI-81,17:06:08,SALESMAN,30
7521,WARD,09-LIP-81,17:06:30,SALESMAN,30
7566,JONES,02-TRA-81,09:24:10,MANAGER,20
7654,MARTIN,28-RUJ-81,17:24:10,SALESMAN,30

Loading:

SQL> $sqlldr scott/tiger@ora10 control=test7.ctl log=test7.log

SQL*Loader: Release 10.2.0.1.0 - Production on Pon Svi 31 09:46:25 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 5

Result:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from emp2;

     EMPNO ENAME                HIREDATE            ETIME               EJOB                     DEPTNO
---------- -------------------- ------------------- ------------------- -------------------- ----------
      7369 SMITH                17.11.2081 00:00:00 01.05.2010 09:14:04 CLERK                        20
      7499 ALLEN                01.05.2081 00:00:00 01.05.2010 17:06:08 SALESMAN                     30
      7521 WARD                 09.06.2081 00:00:00 01.05.2010 17:06:30 SALESMAN                     30
      7566 JONES                02.04.2081 00:00:00 01.05.2010 09:24:10 MANAGER                      20
      7654 MARTIN               28.09.2081 00:00:00 01.05.2010 17:24:10 SALESMAN                     30

SQL>

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458526 is a reply to message #458510]

Mon, 31 May 2010 03:02

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

hi Littlefoot

1- I replaced my control file by your control file (copy&past)

2- would you tell me please what is this (log=test7.log)

3- I altered the session as you mentioned (copy&past)

unfortunately emp2 still empty

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from emp2;

no rows selected

SQL>

please more help

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458532 is a reply to message #458526]

Mon, 31 May 2010 03:08

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I bet you don't query the table you load.

Do NOT explain what you do, SHOW us as Littlefoot did it.
And Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Mon, 31 May 2010 03:08]

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458542 is a reply to message #458532]

Mon, 31 May 2010 03:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try to reproduce every step I did. Copy/paste your session so that we'd see what you did and how.

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458544 is a reply to message #458510]

Mon, 31 May 2010 03:17

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

ok Michel sorry for that

this what happen

I replaced my control file by your control file (copy&past)

load data
infile *
replace
into table emp2
fields terminated by ','
(empno,
ename,
hiredate "to_date(:hiredate, 'dd-mon-yy')",
etime "to_date(:etime, 'hh24:mi:ss')",
ejob,
deptno
)

begindata
7369,SMITH,17-STU-81,09:14:04,CLERK,20
7499,ALLEN,01-SVI-81,17:06:08,SALESMAN,30
7521,WARD,09-LIP-81,17:06:30,SALESMAN,30
7566,JONES,02-TRA-81,09:24:10,MANAGER,20
7654,MARTIN,28-RUJ-81,17:24:10,SALESMAN,30

I altered the session like Littlefoot mentioned (copy&past)

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

this is the execution part

C:\>sqlldr scott/tiger control=C:\external\ctrl.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 11:16:14 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

C:\>

SQL> select * from emp2;

no rows selected

SQL> no result. please more help

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458545 is a reply to message #458544]

Mon, 31 May 2010 03:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, all 5 records are probably in the BAD file because of the invalid month. Use your sample records (months), not mine. My database works in Croatian, yours doesn't.

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458547 is a reply to message #458545]

Mon, 31 May 2010 03:32

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

yes you right, there is .bad file was created in the folder but what do you mean by (Use your sample records (months))

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458550 is a reply to message #458547]

Mon, 31 May 2010 03:51

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

My sample record__: 7369,SMITH,17-STU-81,09:14:04,CLERK,20
Your sample record: 7369,SMITH,17-NOV-81,09:14:04,CLERK,20

The same goes for other records.

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458553 is a reply to message #458550]

Mon, 31 May 2010 04:04

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

that's mean you did not insert the time you change time format

could you solve this plz. I need to insert the time

if i close the session and login again and write a query like this

select * from emp2

the result in both column are date

SQL> select hiredate, etime from emp2;

HIREDATE ETIME
--------- ---------
17-NOV-81 01-MAY-10
01-MAY-81 01-MAY-10
09-JUN-81 01-MAY-10
02-APR-81 01-MAY-10
28-SEP-81 01-MAY-10

SQL>

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458554 is a reply to message #458553]

Mon, 31 May 2010 04:12

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Read carefully. You didn't follow my steps. In SQL*Plus, you have to tell the tool to display date in desired format:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458557 is a reply to message #458554]

Mon, 31 May 2010 04:24

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

I follow you very will let me show you :

this is the control file

load data
infile *
replace
into table emp2
fields terminated by ','
  (empno, 
   ename, 
   hiredate "to_date(:hiredate, 'dd-mon-yy')", 
   etime "to_date(:etime, 'hh24:mi:ss')", 
   ejob, 
   deptno
)

begindata
7369,SMITH,17-NOV-81,09:14:04,CLERK,20
7499,ALLEN,01-MAY-81,17:06:08,SALESMAN,30
7521,WARD,09-JUN-81,17:06:30,SALESMAN,30
7566,JONES,02-APR-81,09:24:10,MANAGER,20
7654,MARTIN,28-SEP-81,17:24:10,SALESMAN,30

this is the session alter :

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL>

this is the command prompt :

C:\>sqlldr scott/tiger control=C:\external\ctrl2.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 12:16:19 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 5

C:\>

now this si the result after insert :

SQL> select hiredate, etime from emp2;

HIREDATE            ETIME
------------------- -------------------
17.11.2081 00:00:00 01.05.2010 09:14:04
01.05.2081 00:00:00 01.05.2010 17:06:08
09.06.2081 00:00:00 01.05.2010 17:06:30
02.04.2081 00:00:00 01.05.2010 09:24:10
28.09.2081 00:00:00 01.05.2010 17:24:10
SQL>

until here every thing it's ok but lets close tthe session and login again

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\windows>sqlplus /nolog

SQL*Plus: Release 10.1.0.4.2 - Production on Mon May 31 12:23:01 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott/tiger;
Connected.
SQL> select hiredate, etime from emp2;

HIREDATE  ETIME
--------- ---------
17-NOV-81 01-MAY-10
01-MAY-81 01-MAY-10
09-JUN-81 01-MAY-10
02-APR-81 01-MAY-10
28-SEP-81 01-MAY-10

SQL>

that's mean the time was not insert

please help me with this problem inserting time

[Updated on: Mon, 31 May 2010 04:28]

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458560 is a reply to message #458557]

Mon, 31 May 2010 04:32

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

that's mean the time was not insert

Actually, no. It means that you don't know what you are doing.

Alter session BEFORE running the SELECT statement:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
31.05.2010

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
31.05.2010 11:31:25

SQL>

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458565 is a reply to message #458560]

Mon, 31 May 2010 04:50

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What crossed my mind: perhaps you'd benefit from reading some documentation.

This is (10g's) SQL*Loader. This is SQL*Plus. This is the complete Oracle documentation. Bookmark version you use and - read it when you need certain information. Hide and seek is funny for a short while, but I don't think that it is a good idea for a long term discussion.

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458568 is a reply to message #458510]

Mon, 31 May 2010 05:06

colla
Messages: 36
Registered: January 2010
Location: ksa

Member

Actually, that's mean you did not test what I'm saying.

simply for more info:
if you modify your session by alter statement you can use this modification (with DML statement) along with your session until you close it.

if you close your session and login again you will not find what did you modify by DML statement

anyway thanks for your time & advice

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458571 is a reply to message #458568]

Mon, 31 May 2010 05:24

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

if you close your session and login again you will not find what did you modify by DML statement

Yes you find it if you appropriately use Oracle.
Read what Littlefoot told you.
I confirm you currently don't know and understand what you do.

As a clue: there is a difference between a date/time and a string that represents the same date/time.

Regards
Michel

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458586 is a reply to message #458571]

Mon, 31 May 2010 06:27

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

colla

if you close your session and login again you will not find ...

But of course you will not. If you ALTER SESSION, then those modifications are valid until session is terminated.

Report message to a moderator

Re: how to load date and time from text file to oracle table through sqlloader [message #458626 is a reply to message #458568]

Mon, 31 May 2010 15:07

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

colla,

What Littlefoot and Michel are telling you is correct and it is clear that you still don't understand. Dates and times are stored together as date datatype. How the data in that date datatype column is displayed and whether the time portion is displayed or not is dependent upon various things. When you login to a session, there is usually a login.sql file that runs automatically and includes an alter session command that sets the default format for the display of date datatypes. Usually that default is just the date without the time. If you issue your own alter session command, you can change that default format and include the display of the time portion. You can also use to_char to change the display. When you logout and login again, the login.sql runs again and changes the default display to just the date again. What you need to understand is that the data is not changed, nothing is lost, the time didn't go away, only the manner in which it is displayed has changed. If you alter your session again, the time will be displayed again. You can see this easily using sysdate, as shown below. Using to_char only affects the command in which it is used and overrides any defaults set by alter sesvion. Using alter session lasts until you logout.

SCOTT@orcl_11g> connect scott/tiger
Connected.
SCOTT@orcl_11g>
SCOTT@orcl_11g> select sysdate from dual;

SYSDATE
---------
31-MAY-10

SCOTT@orcl_11g> select to_char (sysdate, 'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
31.05.2010 13:01:20

SCOTT@orcl_11g> select sysdate from dual;

SYSDATE
---------
31-MAY-10

SCOTT@orcl_11g> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SCOTT@orcl_11g> select sysdate from dual;

SYSDATE
-------------------
31.05.2010 13:01:29

SCOTT@orcl_11g> connect scott/tiger
Connected.
SCOTT@orcl_11g>
SCOTT@orcl_11g> select sysdate from dual;

SYSDATE
---------
31-MAY-10

SCOTT@orcl_11g> select to_char (sysdate, 'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
31.05.2010 13:01:53

SCOTT@orcl_11g> select sysdate from dual;

SYSDATE
---------
31-MAY-10

SCOTT@orcl_11g> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SCOTT@orcl_11g> select sysdate from dual;

SYSDATE
-------------------
31.05.2010 13:02:06

SCOTT@orcl_11g> s

Report message to a moderator

Previous Topic:	How to re-import a 9i dump file to 10g
Next Topic:	ora-00600

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 14:52:22 CST 2025