Solved

oracle load data infile syntax

Posted on 2010-09-17
11
1,230 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 76

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 142

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
 
LVL 76

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 76

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 76

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 76

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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now