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


"1","acct"
"2","it"




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 #259964 is a reply to message #259958] Fri, 17 August 2007 00:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Records are not
optionally enclosed by ' " '
but
optionally enclosed by '"'
Re: help needed with sqlldr [message #259989 is a reply to message #259958] Fri, 17 August 2007 01:54 Go to previous messageGo to next message
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 #260000 is a reply to message #259989] Fri, 17 August 2007 02:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid this might go on and on as you don't know what you are doing. This is the SQL*Loader documentation. It contains many examples as well.

Specifically, regarding your last question, read Benefits of Using Multiple INTO TABLE Clauses section.
Re: help needed with sqlldr [message #260017 is a reply to message #259989] Fri, 17 August 2007 02:50 Go to previous message
Michel Cadot
Messages: 64151
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
Previous Topic: SQL*Load (merged)
Next Topic: exporting table and the sequence associated
Goto Forum:
  


Current Time: Fri Dec 09 15:53:06 CST 2016

Total time taken to generate the page: 0.11043 seconds