| SQL Loader Import [message #70161] |
Tue, 23 April 2002 05:27  |
Jochen
Messages: 6
Registered: December 2000
|
Junior Member |
|
|
hi folks,
i´m trying to import a text file into an oracle db. is it possible to import files, where the values are separated by TABSPACE instead of commas or something like this, if so, how do i tell this the ctl-file?
thanks in advance
|
|
|
|
| Re: SQL Loader Import [message #70165 is a reply to message #70161] |
Tue, 23 April 2002 17:20  |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Here is an example from Oracle technet:
Control File Contents
LOAD DATA
INFILE 'sample.dat' "var 6"
INTO TABLE departments
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
1 (dept_no
dept_name,
dept_mgr COLUMN OBJECT
(name CHAR(30),
age INTEGER EXTERNAL(5),
emp_id INTEGER EXTERNAL(5)) )
Data file (sample.dat)
2 000034101,Mathematics,Johny Q.,30,1024,
000039237,Physics,"Albert Einstein",65,0000,
For more info:
http://technet.oracle.com/doc/server.815/a67792/ch05.htm
|
|
|
|
| Re: SQL Loader Import [message #70178 is a reply to message #70161] |
Thu, 25 April 2002 05:22 |
Jochen
Messages: 6
Registered: December 2000
|
Junior Member |
|
|
Hi Cindy, thanks for the reply. The problem I have is, that the fields are terminated by TABSPACE. My question is, how do I tell the loader what TABSPACE is?
Here an example:
data file
2000034101 Mathematics Johny Q. 30
000039237 Physics Albert Einstein 65
|
|
|
|
| Re: SQL Loader Import [message #70180 is a reply to message #70178] |
Thu, 25 April 2002 07:03 |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Try this:
SQL> create table t
2 (ssn varchar2(11),
3 class varchar2(15),
4 name varchar2(20),
5 age varchar2(3));
Table created.
SQL> desc t
Name Null? Type
--------------- ---- -----------
SSN VARCHAR2(11)
CLASS VARCHAR2(15)
NAME VARCHAR2(20)
AGE VARCHAR2(3)
SQL>
$ more test.ctl
LOAD DATA
INFILE 'test.dat'
APPEND INTO TABLE t
TRAILING NULLCOLS
(SSN position(01:11) CHAR,
CLASS position(15:30) CHAR,
NAME position(31:51) CHAR,
AGE position(52:54) CHAR)
$ more test.dat
2000034101 Mathematics Johny Q. 30
000039237 Physics Albert Einstein 65
$
$ sqlldr login/password errors=20 control=test.ctl log=test.log
SQL*Loader: Release 8.1.7.2.0 - Production on Thu Apr 25 10:42:52 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 2
$
SQL> select count(*) from t;
COUNT(*)
----------
2
SQL> select * from t;
SSN CLASS NAME AGE
------------- ------- --------- -------
2000034101 Mathematics Johny Q. 30
000039237 Physics Albert Einstein 65
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
