| ....Sql Loader.. Another Delima [message #166632] |
Fri, 07 April 2006 05:46  |
orcl_dba
Messages: 84
Registered: March 2005
|
Member |
|
|
Hi all
May i know that what is way sqlldr loads data into the tables.
let me explain my question
Suppose i have 4 tables. each having 10 columns.
These columns are made after viewing a Source which is an Excel Workbook having 4 Different Worksheets.
Each sheet corresponds to a table.
I made CSV files and loads data into tables.
One of the control File looks like
Load data
in file " path of file "
into table A
Append/replace / nothing .. optional
trailing nullcols
( column 1,column2,column3.......column10);
Each column in the excel file has a value.
Suppose one day , ,if worksheet that relates to Table A does not have column 7
will the sqlldr loads correct data in the table.
One of the expereince i had was that it loads column 8 into column 7 , column 9 into 8 and column 10 into 9 and leaving blank column in the end;
The second Question is that if there are 4000 rows in the table
suppose 1000 rows have value for column 7 ,
another 500 rows does not have any value
and then all other rows have v alues.
Will sqlldr load correct data into correct Column,....
I m like surprised to see some strange results of sqlldr that is why i wanted Your experience ...
The third scenerio is
IF column 10 is empty for first 2000 rows, non empty for another 1000 rows and then again empy for another 100 rows.
Will sqlldr loads data where it is non empy.. or will it be chopped as per condition..of
"trailing nullcols"
Kindly share your kind experiences i will be obliged
I need it a bit urgent
//Best Regard
|
|
|
|
| Re: ....Sql Loader.. Another Delima [message #166640 is a reply to message #166632] |
Fri, 07 April 2006 06:21  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
As long as it is properly delimited as your specification it works. Else you can try POSITIONS.
Refer documentation.
oracle@mutation#cat dept.data
10,ACCOUNTING,NEW YORK
20,RESEARCH,
30,,CHICAGO
,OPERATIONS,BOSTON
,,lastcol
,secondcol,
oracle@mutation#sqlldr userid=scott/tiger control=dept.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Fri Apr 7 07:21:06 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 6
oracle@mutation#query mutation scott.dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH
30 CHICAGO
OPERATIONS BOSTON
lastcol
secondcol
6 rows selected.
Regards
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
