We help IT Professionals succeed at work.

oracle load data infile syntax

2,078 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'));
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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
 

Author

Commented:
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.

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
What is variable in the file to be loaded?

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

Author

Commented:
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).
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
Here you are:

1284534900353      1284534900344      1284534900353
1284534901554      1284534901539      1284534901554
1284534903462      1284534903437      1284534903462

these are unix timestamps in milliseconds...
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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

Author

Commented:
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)?

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
solved

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.