Solved

oracle load data infile syntax

Posted on 2010-09-17
11
1,294 Views
Last Modified: 2013-12-18
Hi,

the following is working under mysql, i need to know if something similar is possible in oracle and how to do it:

LOAD DATA INFILE 'data.txt' INTO TABLE db1.mytable
FIELDS TERMINATED BY '\t'
LINES STARTING BY 'LINE\t'
(
    @VARTIME1,
    @VARTIME2,
    @VARTIME2,
)
SET
TIME1 = concat_ws('.', from_unixtime(@VARTIME1 div 1000, '%Y-%m-%d %h:%i:%s'), lpad(@VARTIME1 mod 1000, 3, '0')),
TIME1_UTC = (@VARTIME1 / 1000),
TIME2 = concat_ws('.', from_unixtime(@VARTIME2 div 1000, '%Y-%m-%d %h:%i:%s'), lpad(@VARTIME2 mod 1000, 3, '0'));
0
Comment
Question by:Commsquare
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33701024
This is very similar to Oracle SQL*Loader (sqlldr) syntax.  If you can provide the Oracle table description and some sample data, I can provide a working sql*loader control file.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33701089
Oracle does not have the LOAD DATA syntax, this is specific to MySQL!

these are 2 alternatives I know and use:
* SQL Loader: http://www.orafaq.com/wiki/SQL*Loader_FAQ
* External file: http://psoug.org/reference/externaltab.html
 
0
 

Author Comment

by:Commsquare
ID: 33701153
i don't have the exact table description right now, i just wanted to know how to do stuff like this in oracle. The SQL Loader wiki does not say anything about working with variables.

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33701600
What is variable in the file to be loaded?

You can use some SQL calls in sql Loader to perform some operations.
0
 

Author Comment

by:Commsquare
ID: 33701720
in fact, it's just a transformation that is performed on the timestamps, for example converting unixt timestamp to human readable time. In mysql, the VARTIME1, VARTIME2, VARTIME3 are called variables (i think).
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33701736
SQL Loader can easily convert 'text' in a file into an oracle date or timestamp datatype.

I can make up table names if you can provide some actual text to load.
0
 

Author Comment

by:Commsquare
ID: 33702189
Here you are:

1284534900353      1284534900344      1284534900353
1284534901554      1284534901539      1284534901554
1284534903462      1284534903437      1284534903462

these are unix timestamps in milliseconds...
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33716677
Sorry it took me so long to get back to you on this.  I was swamped Friday.

I borrowed the conversion from:
http://blogs.sun.com/mock/entry/converting_oracle_dates_to_unix

Give the following table:
drop table tab1 purge;
create table tab1(col1 date, col2 date, col3 date);


Here's the sql loader script (I used inline data but you can use a separate file).

After the load I ended up with:

SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

SQL> select * from tab1;

COL1                COL2                COL3
------------------- ------------------- -------------------
09/15/2010 07:15:00 09/15/2010 07:15:00 09/15/2010 07:15:00
09/15/2010 07:15:02 09/15/2010 07:15:02 09/15/2010 07:15:02
09/15/2010 07:15:03 09/15/2010 07:15:03 09/15/2010 07:15:03

load data
   infile *
   replace
   into table tab1
(
	col1 position (1:13) char "TO_DATE('1970-01-01', 'YYYY-MM-DD') + :col1 / 86400000",
	col2 position (20:32) char "TO_DATE('1970-01-01', 'YYYY-MM-DD') + :col2 / 86400000",
	col3 position (39:51) char "TO_DATE('1970-01-01', 'YYYY-MM-DD') + :col3 / 86400000"
)
begindata
1284534900353      1284534900344      1284534900353
1284534901554      1284534901539      1284534901554
1284534903462      1284534903437      1284534903462

Open in new window

0
 

Author Comment

by:Commsquare
ID: 33716770
Hi,

thanks a lot, but it still misses the milliseconds in the timestamp. Can you give me the complete syntax for that (as a separate file preferably)?

0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 33716982
Oracle 'date' columns do not have factional seconds.  You will need to use 'timestamp'.

Try this.  The timestamp conversion came from:
http://forums.oracle.com/forums/thread.jspa?threadID=623852

drop table tab1 purge;
create table tab1(col1 timestamp, col2 timestamp, col3 timestamp);

alter session set nls_timestamp_format='MM/DD/YYYY HH24:MI:SS.FF';



load data
   infile *
   replace
   into table tab1
(
	col1 position (1:13) char "to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr')+numtodsinterval(:col1/1000,'second')",
	col2 position (20:32) char "to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr')+numtodsinterval(:col2/1000,'second')",
	col3 position (39:51) char "to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr')+numtodsinterval(:col3/1000,'second')"
)
begindata
1284534900353      1284534900344      1284534900353
1284534901554      1284534901539      1284534901554
1284534903462      1284534903437      1284534903462

Open in new window

0
 

Author Closing Comment

by:Commsquare
ID: 33751694
solved
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question