Solved

oracle load data infile syntax

Posted on 2010-09-17
11
1,280 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
  • 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
Technology Partners: 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!

 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

735 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