Home » RDBMS Server » Server Utilities » sequence in control file
sequence in control file [message #434496] Wed, 09 December 2009 06:47 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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 Go to previous messageGo to next message
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 #435035 is a reply to message #435032] Sun, 13 December 2009 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
We don't know what the input data actually is.
We don't know exactly what you want as expected/desired results.
Therefore can't offer any possible solution.
Re: sequence in control file [message #435054 is a reply to message #435032] Mon, 14 December 2009 01:04 Go to previous messageGo to next message
baktash.siddiqi
Messages: 3
Registered: December 2009
Location: Kabul,Afghanistan
Junior Member
Hi Guys,

I've schedule my sqlldr in crontab but after running shows "command not found"
Please advice....Thanks
Re: sequence in control file [message #435056 is a reply to message #435054] Mon, 14 December 2009 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please advice

Correctly set your PATH.
Remember .profile is not execute in cron. Set your variables inside the scheduled script.

Regards
Michel
Re: sequence in control file [message #435058 is a reply to message #435056] Mon, 14 December 2009 01:27 Go to previous messageGo to next message
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
Re: sequence in control file [message #435061 is a reply to message #435058] Mon, 14 December 2009 01:53 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORACLE_HOME is not set
PATH does not contain $ORACLE_HOME/bin

Regards
Michel
Previous Topic: How commit happens in SQL Loader
Next Topic: 10g export (merged 3)
Goto Forum:
  


Current Time: Thu Sep 29 22:40:31 CDT 2016

Total time taken to generate the page: 0.27445 seconds