| SQL LOADER : How to create a unique row id ? [message #368144] |
Fri, 08 September 2000 11:22  |
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  |
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 |
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
|
|
|
|
|
|
|
|
| Can you help again ? SQL LOADER and Dates [message #368152 is a reply to message #368149] |
Thu, 14 September 2000 03:52 |
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 |
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 #368156 is a reply to message #368149] |
Thu, 14 September 2000 09:10 |
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
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
