Unique sequence number per datafile using SQL Loader

[radhika....@gmail.com]

Here's the requirement:

I have a datafile with say 500 records to be loaded. Each time I load
the file, I need to generate a sequence number unique to the file,
which means all the 500 records loaded from that file should have the
same sequence number.
The next file to be laoded will have another unique number.
I thought of using to_number(to_char(sysdate, 'YYYYMMDDHHMMSS') for a
unique sequence. however, this does not generate one unique number for
a file.

Is there any other way to handle this requirement in the control file
itself.

[gazzag]

Look into using Oracle sequences.

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm#i2067093

HTH

-g

[radhika....@gmail.com]

That will create a new sequence number for each record.
What I need is just one sequence number for each file. (All records in
that file will have the same number)
The next file will have another number...so on and so forth.

[Andy Hardy]

radhika....@gmail.com wrote:
> That will create a new sequence number for each record.
> What I need is just one sequence number for each file. (All records in
> that file will have the same number)

Only if you assign the sequence number per record, you could just pick
the sequence number once per load.

[radhika....@gmail.com]

Could you please let me know how to go about that idea..

[Maxim Demenko]

radhika....@gmail.com schrieb:

One possibility would be:

SQL> CREATE TABLE T
2 (
3 FILE_ID NUMBER,
4 ID NUMBER,
5 TEXT VARCHAR2(30)
6 )
7 /

Table created.

SQL> CREATE SEQUENCE t_seq
2 /

Sequence created.

SQL> CREATE OR REPLACE PACKAGE t_Pck IS
2 g_t NUMBER;
3 FUNCTION Ret_t RETURN NUMBER;
4 END;
5 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY t_Pck IS
2 FUNCTION Ret_t RETURN NUMBER IS
3 BEGIN
4 RETURN g_t;
5 END;
6 BEGIN
7 SELECT t_Seq.NEXTVAL INTO g_t FROM Dual;
8 END;
9 /

Package body created.

SQL> set head off verify off feed off show off trimspool on pages 0
SQL> spool f1.dat
SQL> select rownum||','||table_name from tabs where rownum<=4;
1,T
2,DEPT
3,EMP
4,BONUS
SQL> spool off
SQL> !cp f1.dat f2.dat

SQL> !cp f1.dat f3.dat

SQL> !cat f1.ctl
load data into table t
append
fields terminated by ','
TRAILING NULLCOLS
(id,
text,
file_id "t_pck.ret_t")

SQL> !sqlldr userid=scott/tiger data=f1.dat control=f1.ctl log=f1.log

SQL*Loader: Release 10.2.0.2.0 - Production on Wed Oct 25 22:38:37 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 6

SQL> !sqlldr userid=scott/tiger data=f2.dat control=f1.ctl log=f1.log

SQL*Loader: Release 10.2.0.2.0 - Production on Wed Oct 25 22:38:50 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 6

SQL> !sqlldr userid=scott/tiger data=f3.dat control=f1.ctl log=f1.log

SQL*Loader: Release 10.2.0.2.0 - Production on Wed Oct 25 22:39:00 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 6

SQL> set echo on feed on term on head on pages 999
SQL> select count(*),file_id from t group by file_id;

COUNT(*) FILE_ID
---------- ----------
4 1
4 2
4 3

3 rows selected.

Best regards

Maxim

[tony_becky_mikey_verizon_news]

<radhika....@gmail.com> wrote in message
news:1161787225.9...@e3g2000cwe.googlegroups.com...

How about this.. Build a table that will hold one row per file you upload..
Build a sequence associated to this table
On this table, build an insert trigger that is fired when you add a new row
to file table
When you have a new file to upload, add a row for each new data table, and
take the new id as the base for your "key"

Hope this helps!

Tony Miller
Monroe, WA

[St...@x.com]

In article <1161787225.9...@e3g2000cwe.googlegroups.com>,
radhika....@gmail.com says...

Build a table exactly like the target table. Include a column for the
sequence number. Load the data into that table. Find the next seq number
by selecting the highest seq number from the target table and add 1
(one) to it. Put the seq number value into the load table's seq number.
Transfer the data from the from the load table to the target table.

Alternatively, Load the data into to target. Have a column for the seq
number in that table. It is null after the load finishes. Find the
highest seq value from the target table, add one to it, update the null
rows to the seq number.

[Ed Prochak]

St...@x.com wrote:
[]

> >
> Build a table exactly like the target table. Include a column for the
> sequence number. Load the data into that table. Find the next seq number
> by selecting the highest seq number from the target table and add 1
> (one) to it. Put the seq number value into the load table's seq number.
> Transfer the data from the from the load table to the target table.
>
> Alternatively, Load the data into to target. Have a column for the seq
> number in that table. It is null after the load finishes. Find the
> highest seq value from the target table, add one to it, update the null
> rows to the seq number.

Gagh!
Reasons why this is not a good idea:
1. the load gets slower as the target table gets larger. (because each
load must do a new search of the target table for the MAX value).

B. only one file can be loaded at a time (two loaded at the same time
both get the same MAX number.)

III. assigning the "file number" is done on the application level after
sql*loader is done. (forget to run the number assignment step and you
have either rows with no number or rows from two different files with
the same number).

Bottom line: use a sequence.
Ed