Re: How do I ensure order of rows loaded by SQL Loader?

From: Puneet <puneet.bansal_at_wipro.com>
Date: 14 Nov 2003 03:13:36 -0800
Message-ID: <4c276d80.0311140313.243de656@posting.google.com>

Thank you Jim,John,Holger and Yong for showing me the right way. The 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.

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0311130607.154b5f08_at_posting.google.com>...
> puneet.bansal_at_wipro.com (Puneet) wrote in message news:<4c276d80.0311122127.562b3607_at_posting.google.com>...
> > Hi everybody !
> > 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?
>
> 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
Received on Fri Nov 14 2003 - 05:13:36 CST