Home » RDBMS Server » Server Utilities » SQL LOADER : How to create a unique row id ?
SQL LOADER : How to create a unique row id ? [message #368144] Fri, 08 September 2000 11:22 Go to next message
Chella
Messages: 25
Registered: September 2000
Junior Member
I am dumping a flat file into a Oracle table but the flat file does not have a unique row id for each record. So, I created a column in the Oracle table (recid NUMBER) and would like to know how SQL LOADER can put a sequce number into this field ? I have tried writting a control file like this. is this correct ?

A detailed explanation much appreciated as I am new to SQL LOADER. Thank U in advance
LOAD DATA
INFILE 'stmt.txt'
APPEND
INTO TABLE tblMailboxData
(
RecID SEQUENCE(1),
CustSorCode POSITION(1:6),
CustAcNumber POSITION(7:14),
TransCode POSITION(16:17),
RemSortCode POSITION(18:23),
RemAcNumber POSITION(24:31),
Amount POSITION(36:46),
RemName POSITION(47:64),
Reference POSITION(65:82),
Beneficiary POSITION(83:100),
Transdate POSITION(101:106),
)
Re: SQL LOADER : How to create a unique row id ? [message #368148 is a reply to message #368144] Tue, 12 September 2000 17:03 Go to previous messageGo to next message
Bala
Messages: 205
Registered: November 1999
Senior Member
Add the sequence ID at the bottom like this

LOAD DATA
INFILE 'stmt.txt'
APPEND
INTO TABLE tblMailboxData
(
CustSorCode POSITION(1:6),
CustAcNumber POSITION(7:14),
TransCode POSITION(16:17),
RemSortCode POSITION(18:23),
RemAcNumber POSITION(24:31),
Amount POSITION(36:46),
RemName POSITION(47:64),
Reference POSITION(65:82),
Beneficiary POSITION(83:100),
Transdate POSITION(101:106,
RecID sequence(max,1)
)

this will work
Good luck
Bala
Re: SQL LOADER : How to create a unique row id ? [message #368149 is a reply to message #368148] Wed, 13 September 2000 03:56 Go to previous messageGo to next message
Chella
Messages: 25
Registered: September 2000
Junior Member
Bala

Thank U for the reply. What does the parameter MAX in SEQUENCE(MAX,1) does ? Does it check the maximum recid that exists in the Oracle table and then increment the RecID by 1 ? If so, that's good.

Would U also know whether I can call a stored PL/SQL function in a control file ? Help is much appreciated.

Chella Rathakrishnan
Re: SQL LOADER : How to create a unique row id ? [message #368150 is a reply to message #368149] Wed, 13 September 2000 08:42 Go to previous messageGo to next message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi,
Thats what the Max parameter does. It is use full when you append into a table. If you do just reload then its not usefull but anyway it is harmless.
You can use plsql function.
ex:
col1 "function(:col1)"

Good luck
Bala
Thank U. How to create a unique row id ? [message #368151 is a reply to message #368149] Wed, 13 September 2000 08:55 Go to previous messageGo to next message
Chella
Messages: 25
Registered: September 2000
Junior Member
I tried the SEQUENCE(MAX, x) and it works. Will be trying the Function call and hopefully it'll work. If not, will write back. Thank U for the help.
Can you help again ? SQL LOADER and Dates [message #368152 is a reply to message #368149] Thu, 14 September 2000 03:52 Go to previous messageGo to next message
Chella
Messages: 25
Registered: September 2000
Junior Member
Hi,

I have a field (say DateField) in the text file which is like '98327'. This is actually the day number within the year 98. So in the control file, I have this function
TO_DATE (:DateField, 'YYDDD') which converts to dd-Mon-yy, say 13-Nov-98. Which is fine.

But I would like the year to be a 4 digit to avoid the Y2K confusion. So, when it loads it should look like 13-Nov-1998. How should I change the code in the controld file to achieve this result ? I tried various things but it loads it as 2098 and not as 1998.

Hope you could help me again. Thank you in advance
Re: Can you help again ? SQL LOADER and Dates [message #368153 is a reply to message #368149] Thu, 14 September 2000 08:18 Go to previous messageGo to next message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi,

You can use to_date(:datefield, 'RRDDD')
RR is a century window which will assume
year less than 49 as 20th century
and more than 49 as 19th.

It will work as long as you don't have any date
below 1949, but if you have, then
you have to load the data into a temp table.

And call the date field as varchar2(5), and load the date as charecters.
Then, write a PLSQL proc or something
if substr(datefield, 1, 2) <> '00' than
datefield = '19' || datefield
else datefield = '20' || datefield.

Even this is not a scalble method.

I can't think of any other solution.

Bala
Re: Can you help again ? SQL LOADER and Dates [message #368154 is a reply to message #368149] Thu, 14 September 2000 08:55 Go to previous messageGo to next message
Chella
Messages: 25
Registered: September 2000
Junior Member
Tried TO_DATE(:datefield, 'RRDDD'). But it still loads the datefield as 'dd-Mon-YY'. I don't have any dates below 1949 and I am puzzled as to why it's not working! Thank U again for your help
It worked. Thank U - TO_CHAR(TO_DATE( , ), ) [message #368155 is a reply to message #368149] Thu, 14 September 2000 09:03 Go to previous messageGo to next message
Chella
Messages: 25
Registered: September 2000
Junior Member
Your suggestion worked but I just had to add TO_CHAR and load it as a character field. Thank U...
Re: Can you help again ? SQL LOADER and Dates [message #368156 is a reply to message #368149] Thu, 14 September 2000 09:10 Go to previous messageGo to next message
Bala
Messages: 205
Registered: November 1999
Senior Member
If you just do
select datefield from table1;
19-NOV-98
Because it is the default display format, but actually the date is stored internally as 4 digit. To test that
select to_char(datefield, 'MM-DD-YYYY') from table1
11-19-1998

So all your calcualtion and everything on datefield will be correct since you have the four digit year stored internally.

If you use to_date(:datefield, 'YYDDD') in the sql loader and
then do a select to_char(datefield, 'MM-DD-YY')
from table1
11-19-2098

which is not correct.
so since you don't have year below 1949 you can use RRDDD, which will give no problem.

Bala
unique [message #368316 is a reply to message #368149] Thu, 09 November 2000 21:15 Go to previous message
Derek
Messages: 3
Registered: November 2000
Junior Member
how do i get rid of it?
Previous Topic: setting rollback transaction in sqlldr
Next Topic: Oracle webassitant
Goto Forum:
  


Current Time: Thu May 19 23:24:12 CDT 2022