Home » RDBMS Server » Server Utilities » help needed with sqlldr
help needed with sqlldr [message #259958] |
Fri, 17 August 2007 00:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gautamvv
Messages: 254 Registered: June 2005
|
Senior Member |
|
|
i am trying to load data from a .dat file into a dept table
create table dept(dno number(7),dname varchar2(40));
so i have a control file tctl.ctl, and a data file
tinput.dat
tctl.ctl
load data
into table dept
append
fields terminated by ',' optionally enclosed by ' " '
(dno,
dname)
tinput.dat
note that for dno, the data is in format "1","2" etc
they are double quoated
now i do
sqlldr userid=gautam/gautam@gwcm control=tctl.ctl data=tinput.dat
but in the log file i get
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Aug 17 10:36:51 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: tctl.ctl
Data File: tinput.dat
Bad File: tinput.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table DEPT, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DNO FIRST * , CHARACTER
Enclosure string : ' " '
DNAME NEXT * , CHARACTER
Enclosure string : ' " '
Record 1: Rejected - Error on table DEPT, column DNO.
ORA-01722: invalid number
Record 2: Rejected - Error on table DEPT, column DNO.
ORA-01722: invalid number
Table DEPT:
0 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
but actually i am about to implement something similar to this
for my client, and since i never used sqlldr, i am trying this
sample one, and my original input file would contain
data in the same fashion,
so how to make sure that "1","2" gets inserted int dno column?
|
|
|
|
Re: help needed with sqlldr [message #259989 is a reply to message #259958] |
Fri, 17 August 2007 01:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gautamvv
Messages: 254 Registered: June 2005
|
Senior Member |
|
|
thank you
now i am trying to do this
i have emp table
create table emp(eno number(4),dno number(4),dname varchar2(20))
my dat file is
"1","acct"
"2","1","acct"
"1","it"
"2","2","it"
note that all "1" are the dnos, and all "2" are enos
actually my original requirement is that in the input file,
one line is for organization, the next line is for the user
of that organization
so i need to populate data in two tables, the first line is for
the organization table, second line, for the usertable
and the format is such that for org level info, its "1"
for user level info, its "2"
similarly i want in the input file, the first line should go to
dept, second line should go to emp...
is it possible to extend this?
i tried doing this
load data
into table dept
append
fields terminated by ',' optionally enclosed by '"'
(dno,
dname)
into table emp
append
fields terminated by ',' optionally enclosed by '"'
(eno,
dno,
dname)
which is wrong, and am not able to populate...
log is
Table DEPT, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
Table EMP, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ENO NEXT * , O(") CHARACTER
DNO NEXT * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
Record 1: Rejected - Error on table EMP, column ENO.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table EMP, column DNO.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table EMP, column ENO.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table EMP, column DNO.
Column not found before end of logical record (use TRAILING NULLCOLS)
Table DEPT:
0 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
|
|
|
|
Re: help needed with sqlldr [message #260017 is a reply to message #259989] |
Fri, 17 August 2007 02:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can also use an external table that supersede both definitions and then use an "insert select" to fill the tables.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Fri Sep 06 23:45:36 CDT 2024
|