Home » RDBMS Server » Server Utilities » how to load date and time from text file to oracle table through sqlloader
how to load date and time from text file to oracle table through sqlloader [message #458510] |
Mon, 31 May 2010 02:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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>
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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.
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #458568 is a reply to message #458510] |
Mon, 31 May 2010 05:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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
|
|
|
Goto Forum:
Current Time: Mon Sep 09 22:18:50 CDT 2024
|