| sequence in control file [message #434496] |
Wed, 09 December 2009 06:47  |
mohan1760
Messages: 59
Registered: June 2008
|
Member |
|
|
I want to add condition in sequence_number so as it should only increase for perticular iata_code.
i have tried
SEQUENCE_NUMBER SEQUENCE(MAX,1))
but it increases for each record , not depending upon perticular
iata_code.
sample control file is as below.
LOAD DATA
INFILE 'C:\Documents and Settings\mohan_patil\Desktop\raw data migr\05_Extract.txt'
BADFILE 'C:\Documents and Settings\mohan_patil\Desktop\raw data migr\05_Extract.bad'
DISCARDFILE 'C:\Documents and Settings\mohan_patil\Desktop\raw data migr\05_Extract.dsc'
INTO TABLE "CSP"."T_COUNTRY_HISTORY_MIGR"
INSERT
(IATA_CODE POSITION (3:5),
HST_CARR_STAT_FLAG POSITION (6:6),
CASS_COMM_PERCENT POSITION (7:11),
LOCAL_BILLING_IND POSITION (12:12),
NAME POSITION (13:37),
SHIPPER_BILL_OFFS POSITION (38:39),
PRIMARY_CCY_CODE POSITION (40:42),
SECONDARY_CCY_CODE POSITION (43:45),
HOST_INF_SEQ_NUM POSITION (46:53),
CHGS_COLL_ACPT_IND POSITION (54:54),
CHGS_COLLECT_PCT POSITION (55:59),
CHGS_COLLECT_CCY POSITION (60:62),
CHGS_COLL_MIN_AMT POSITION (63:77),
UPDATE_DATE POSITION (78:85) DATE "MMDDYYYY",
UPDATE_TIME POSITION (86:93) DATE "MMDDYYYY",
USER_ID POSITION (94:101),
CARRIER_PREFIX POSITION (102:105),
OVERRIDING_COMM POSITION (106:110),
IATA_AREA POSITION (111:111),
EFFECTIVE_DATE POSITION (112:119) DATE "MMDDYYYY",
EXPIRY_DATE POSITION (120:127),
COUNTRY_IATA_CODE POSITION (3:5),
SEQUENCE_NUMBER SEQUENCE(MAX,1))
Thanks in advance...
|
|
|
|
| Re: sequence in control file [message #434526 is a reply to message #434496] |
Wed, 09 December 2009 09:26  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version
I do not think you can do what you desire with SQLLDR.
If this is correct, a viable alternative would be EXTERNAL TABLE,
but you'll then have to write your own load code.
[Updated on: Wed, 09 December 2009 09:26] Report message to a moderator |
|
|
|
| Re: sequence in control file [message #435032 is a reply to message #434526] |
Sun, 13 December 2009 22:32 |
mohan1760
Messages: 59
Registered: June 2008
|
Member |
|
|
os: microsoft xp
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
I hope you guys can help....
|
|
|
|
|
|
|
|
|
|
| Re: sequence in control file [message #435058 is a reply to message #435056] |
Mon, 14 December 2009 01:27 |
baktash.siddiqi
Messages: 3
Registered: December 2009
Location: Kabul,Afghanistan
|
Junior Member |
|
|
My Script is below please kindly advice what is wrong with this ...
#!/bin/bash
# Veriables
LOGDIR=/export/home/oracle/VOM_LOGS
DTEXT=`date '+%U%m%d'`
SCRIPTDIR=/export/home/oracle/SCRIPTS
INPUTDIR=/backup/loaded_voms/INPUT
BACKUPDIR=/backup/loaded_voms
DATETIME=`date +%Y%m%d%H%M`
ORACLE_SID=billing123; export ORACLE_SID
sqlldr TSI/TSI control=${SCRIPTDIR}/voms.ctl direc
mv ${INPUTDIR}/VOMS_LOAD_DATA.txt ${BACKUPDIR}/VOM
gzip /${BACKUPDIR}/VOMS_LOAD_DATA.loaded$DATETIME
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
