How do I ensure order of rows loaded by SQL Loader?
Puneet
I am loading a CSV file into Oracle 8i table using SQL Loader
utility. The data is in such a format that the order of the records is
of paramount importance. But when I retrieve the data from the table
it can be in any order, isn't it?
My doubts are:
Does SQL Loader always load the records in the same order as that of
the data file?
If yes then how can I ensure that when I retrive the records they are
always in the same order as the one specified in CSV file? (The table
is just a temp table and it doesn't have any primary key or any column
I can order by.) Will 'order by rowid' solve the problem?
Thanks for your time and help.
Puneet
Jim Kennedy
right order is? By definition the data in a table is unordered and you
select a group or ret of records and unless you specify an order then you
can't guarantee the order it comes back. Why not have an extra column that
is the seeded with a sequence and order by that?
Why is the order not important? If you don't have a primary key how do you
distinguish one row from another?
Jim
"Puneet" <puneet...@wipro.com> wrote in message
news:4c276d80.03111...@posting.google.com...
Puneet
The correct order is the order specified in the CSV file. The first
record is the name of a person with a tag attached to it 'Contact'.
The next 4 records are just names without the keyword 'Contact' in
them, so I know that these 4 people are associated with the first
'Contact'. The next record is again a 'Contact', so I know that this
is a new contact and then again there are people associated with this
contact. So if I traverse by order I know which people to associate
with what contact. So that's why the order bocomes important. You are
right that I can define a column having sequence numbers but the
problem is I am loading the data using sql loader. How do I insert
sequence numbers while loading the data from sql loader? Is there any
way a sequence number is auto inserted in a column?
Thanks.
Puneet
"Jim Kennedy" <kennedy-downwit...@attbi.net> wrote in message news:<P5Fsb.135158$mZ5.935009@attbi_s54>...
John Smith
> Thanks Jim for your response.
> The correct order is the order specified in the CSV file. The first
> record is the name of a person with a tag attached to it 'Contact'.
> The next 4 records are just names without the keyword 'Contact' in
> them, so I know that these 4 people are associated with the first
> 'Contact'. The next record is again a 'Contact', so I know that this
> is a new contact and then again there are people associated with this
> contact. So if I traverse by order I know which people to associate
> with what contact. So that's why the order bocomes important. You are
> right that I can define a column having sequence numbers but the
> problem is I am loading the data using sql loader. How do I insert
> sequence numbers while loading the data from sql loader? Is there any
> way a sequence number is auto inserted in a column?
why don't you append a column in your csv file containing a sequence# ?
Yong Huang
Hi, Puneet,
SQL*Loader reads one line at a time in its datafile in that order. But
what's loaded in a regular table is unordered. That's why a regular
table is also called a heap table. If a later-loaded row can be fit in
a hole in that heap, it may go there. Using direct-path load should
help. But even then Oracle can't guarantee the data retrieved serially
will be shown in the order as in the SQL*Loader datafile. As others
suggested, you need a sequence number to order the data in Oracle. You
wouldn't think you had this sequence in your CSV. But it *is* in
there, implicitly by definition of a text file you read sequentially.
Using 'order by rowid' won't help either. Think of what's in a rowid
and the concept of a heap table.
Yong Huang
Jim Kennedy
number. Or you can use external tables in 9i to process the file directly.
Holger Baer
> Thanks Jim for your response.
> The correct order is the order specified in the CSV file. The first
> record is the name of a person with a tag attached to it 'Contact'.
> The next 4 records are just names without the keyword 'Contact' in
> them, so I know that these 4 people are associated with the first
> 'Contact'. The next record is again a 'Contact', so I know that this
> is a new contact and then again there are people associated with this
> contact. So if I traverse by order I know which people to associate
> with what contact. So that's why the order bocomes important. You are
> right that I can define a column having sequence numbers but the
> problem is I am loading the data using sql loader. How do I insert
> sequence numbers while loading the data from sql loader? Is there any
> way a sequence number is auto inserted in a column?
>
> Thanks.
> Puneet
Goto
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm#1008282
and read up on recnum or sequence. They will give you what you need.
You will obviously have to create a table that has one column more than
your csv file, to take the sequence number.
HTH,
Holger
Puneet
problem is solved. In fact it is a neat little discovery (at least for
me)!
I've added one more column to the table to hold the sequence numbers.
I defined a sequence and have defined one function to return
appropriate sequence numbers:
create function cdb_generate_sq(av_rowtype varchar2) return
number
is
vn_sq_no number(14);
begin
if av_rowtype = 'CONTACT' then
select sq_na_contact.nextval into vn_sq_no from dual;
else
select sq_na_contact.currval into vn_sq_no from dual;
end if;
return vn_sq_no;
end cdb_generate_sq;
/
show errors
And in the control file of the sql loader I've called this function
while loading the data. My control file now looks like this
options (errors=2000000)
LOAD DATA
replace
INTO TABLE
TEMP_CONTACT_EXCEL
fields terminated by ","
TRAILING NULLCOLS
(ROW_TYP ,
CNTCT_TYP_ID ,
CNTCT_FIRST_NM ,
CNTCT_LAST_NM ,
CNTCT_MID_INIT ,
SEQ INTEGER "cdb_generate_sq(:ROW_TYP)"
)
Note the last line.
This inserts the sequence numbers as it loads the records based on the
value of the ROW_TYP column which can either be 'CONTACT' or not. Now
while accessing the records I can order them by sequnece numbers and
be sure that I get them in the correct order.
This was an eye opener for me and I think many others may not know
about this that's why I've shared this with everybody.
Puneet.
yon...@yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.03111...@posting.google.com>...
Joel Garry
> The correct order is the order specified in the CSV file. The first
> record is the name of a person with a tag attached to it 'Contact'.
> The next 4 records are just names without the keyword 'Contact' in
> them, so I know that these 4 people are associated with the first
> 'Contact'. The next record is again a 'Contact', so I know that this
> is a new contact and then again there are people associated with this
> contact. So if I traverse by order I know which people to associate
> with what contact. So that's why the order bocomes important. You are
> right that I can define a column having sequence numbers but the
> problem is I am loading the data using sql loader. How do I insert
> sequence numbers while loading the data from sql loader? Is there any
> way a sequence number is auto inserted in a column?
You might consider reading up on "normalization" "entities" and
"attributes" so you don't unintentionally wind up with a denormalized
table in a relational database.
jg
--
@home.com is bogus.
http://www.datamodel.org/NormalizationRules.html
Michael J. Moore
Joel's advice and "normalize" the data. Maybe you had a good reason but too
often I see people adding sequence numbers when what they should have done
is reduced the data to 3rd normal form and created tables and constraints
accordingly.
With your solution, your programmers will now have to code logic to detect
if there are any 'associates' before attempting to delete a 'contact' -
delete anomaly, and detect if there is a corresponding 'contact' before
inserting an 'associate' - update anomaly. Also you now have issues of what
happens when associates are delete leaving a gap in your sequence numbers.
Maybe your data is static so you don't have to worry about these problems.
If so, you are home free. But if programmers need to maintain this data with
updates, inserts and deletes, you have placed a heavy burden on them by not
normalizing the data.
Regards,
Mike
"Puneet" <puneet...@wipro.com> wrote in message
news:4c276d80.03111...@posting.google.com...
Puneet
Of course I understand the importance of normalized data. Actually
the table in which I am loading the data is just a temporary table.
There are a lot of validations to be performed on the data in the 'as
is' format. That's why I've loaded them into a single table. Once the
data passes the validations I've written an oracle procedure that
reads the data from this table and enters them into some 6-7
normalized tables. The temp table data is then deleted. Straightway
entering the data from SQL Loader to these tables would have been
difficult.
So I've not lost out on the 'normalization' part.
Puneet
"Michael J. Moore" <NOhica...@comcast.net> wrote in message news:<J0Otb.167960$ao4.543251@attbi_s51>...