[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1410
  • Last Modified:

oracle load data infile syntax

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
Commsquare
Asked:
Commsquare
  • 5
  • 5
1 Solution
 
slightwv (䄆 Netminder) 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
CommsquareAuthor 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.

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

You can use some SQL calls in sql Loader to perform some operations.
0
 
CommsquareAuthor 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).
0
 
slightwv (䄆 Netminder) 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.
0
 
CommsquareAuthor Commented:
Here you are:

1284534900353      1284534900344      1284534900353
1284534901554      1284534901539      1284534901554
1284534903462      1284534903437      1284534903462

these are unix timestamps in milliseconds...
0
 
slightwv (䄆 Netminder) 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

0
 
CommsquareAuthor 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)?

0
 
slightwv (䄆 Netminder) Commented:
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
 
CommsquareAuthor Commented:
solved
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now