Re: Unique sequence number per datafile using SQL Loader
Date: Wed, 25 Oct 2006 22:43:55 +0200
Message-ID: <ehoiaa$9de$02$1_at_news.t-online.com>
radhika.penagonda_at_gmail.com schrieb:
> Could you please let me know how to go about that idea.. > > radhika.penagonda_at_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)
>> The next file will have another number...so on and so forth.
>>
>>
>>
>> gazzag wrote:
>>> radhika.penagonda_at_gmail.com wrote:
>>>> 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.
>>> Look into using Oracle sequences.
>>>
>>> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm#i2067093
>>>
>>> HTH
>>>
>>> -g
>
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 Received on Wed Oct 25 2006 - 22:43:55 CEST