Re: about sql*loader load sequence #

On May 1, 5:07 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On May 1, 4:47 pm, esthershe..._at_yahoo.com wrote:
>
> > Hi,
>
> > I'm new in Oracle. I have a question for sql*loader.
>
> > If I had a columnA in table , I need to load sequence number into that
> > columnA.
>
> > I know I could use sequence option to load, but there is no way to
> > reset the sequence if the number reach some high value. Or if I need
> > load data every month, the next load want to know the last value ..
>
> > the only way is create sequence in database, and reference sequence in
> > load control file.
>
> > Am I right ? Want to get some suggestion from experienced DBA.. Thanks.
>
> Take a look athttp://asktom.oracle.comdo a search and include the
> words sequence and sqlldr ...
>
> I am sure you will get some good examples

The sqlldr utility has several chapters devoted to it in the Utilities manual.

Just use an Oracle database sequence object and you do not have to keep track of the last number the database will. Look at the following example:

LOAD DATA
    INFILE location_of_dat_file
    INTO TABLE xyz

    (  rec_no                       "your_sequence.nextval",
       date_inserted                "to_char(SYSDATE, 'dd-mon-yyyy
hh24:mi')",
       col1         POSITION(1:5)   "col1",
       col2         POSITION(6:15)  "col2",
       data3        POSITION(16:22) "col3"

Notice the use of a database sequence for column rec_no.

You can also use a before insert trigger on the table to populate a column using a sequence.

HTH -- Mark D Powell -- Received on Tue May 01 2007 - 18:25:54 CDT