Home » RDBMS Server » Server Utilities » Loading data into multiple tables
Loading data into multiple tables [message #201888] Tue, 07 November 2006 04:06 Go to next message
laksha
Messages: 42
Registered: June 2006
Member
I want to load data from one file delimited by ^ into multiple tables.
The data is to be loaded in different tables using the 2nd field in the file.FH,BH,CD,DD
Each corresponding records in the file have different stucture and each table is created accordingly.

The problem I am facing is that the records satisfying the first when clause are loaded into the corresponding table. But for all the other when clauses the records are discarded because of "failed when clauses".

Attaching table creation scripts,file(attched),control file,parfile

My control file is here

LOAD DATA
INFILE 'ABCD.txt'
APPEND
INTO TABLE LAXMAN_ETDS_FH
WHEN RECORD_TYPE='FH'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NUMBER ,
RECORD_TYPE ,
FILE_TYPE ,
UPLOAD_TYPE ,
FILE_CREATION_DATE DATE( Cool "DDMMYYYY",
FILE_SEQUENCE_NUMBER ,
UPLOADER_TYPE ,
TAN_OF_DEDUCTOR ,
TOTAL_NO_OF_BATCHES ,
RECORD_HASH ,
FVU_VERSION ,
FILE_HASH ,
SAM_VERSION ,
SAM_HASH ,
SCM_VERSION ,
SCM_HASH
)
INTO TABLE LAXMAN_ETDS_BH
WHEN RECORD_TYPE='BH'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NUMBER ,
RECORD_TYPE ,
BATCH_NUMBER ,
COUNT_OF_CHLNRECORDS ,
FORM_NUMBER ,
TRANSACTION_TYPE ,
BATCH_UPDATION_INDICATOR ,
ORIGINAL_RRR_NO ,
PREVIOUS_RRR_NUMBER ,
RRR_NUMBER ,
RRR_DATE ,
LAST_TAN_DEDUCTOR ,
TAN_OF_DEDUCTOR ,
FILLER1 ,
PAN_OF_DEDUCTOR ,
ASSESSMENT_YR ,
FINANCIAL_YR ,
PERIOD ,
NAME_OF_DEDUCTOR ,
DEDUCTOR_BRANCH ,
DEDUCTOR_ADDRESS1 ,
DEDUCTOR_ADDRESS2 ,
DEDUCTOR_ADDRESS3 ,
DEDUCTOR_ADDRESS4 ,
DEDUCTOR_ADDRESS5 ,
DEDUCTOR_STATE ,
DEDUCTOR_PINCODE ,
DEDUCTOR_EMAILID ,
DEDUCTOR_STD ,
DEDUCTOR_PH_NO ,
ADDRESS_CAHNGE_SINCE_LAST_RET ,
DEDUCTOR_TYPE ,
PERSON_RESPONSIBLE_DEDUCTION ,
DESIGNATION ,
RESP_PERSON_ADDRESS1 ,
RESP_PERSON_ADDRESS2 ,
RESP_PERSON_ADDRESS3 ,
RESP_PERSON_ADDRESS4 ,
RESP_PERSON_ADDRESS5 ,
RESP_PERSON_STATE ,
RESP_PERSON_PIN ,
RESP_PERSON_EMAIL ,
REMARK ,
RESP_PERSON_STD_CODE ,
RESP_PERSON_PHONE_NO ,
CHANGE_ADDR_SINCE_LAST_RETURN ,
BATCH_TOTAL_DEPOSIT_AMT ,
TDSCIRCLE ,
COUNT_SALARY_DET_RECORDS ,
BATCH_TOTAL_GROSS_INCOME ,
AO_APPROVAL ,
AO_APPROVAL_NUMBER ,
RECORD_HASH
)
INTO TABLE LAXMAN_ETDS_CD
WHEN RECORD_TYPE='CD'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NUMBER ,
RECORD_TYPE ,
BATCH_NUMBER ,
CHLN_DET_RECORD_NO ,
COUNT_DEDUCTEE ,
NIL_CHLN_INDICATOR ,
CHLN_UPDATION_INDICATOR ,
FILLER_2 ,
FILLER_3 ,
FILLER_4 ,
LAST_BANK_CHLN_NO ,
BANK_CHALLAN_NO ,
LAST_TRANSFER_VCH_NO ,
TRANSFER_VCH_NO ,
LAST_BANK_BRANCH_CODE ,
BANK_BRANCH_CODE ,
LAST_DATE_BANK_CHLN_NO ,
DATE_BANK_CHLN_NO DATE( Cool "DDMMYYYY",
FILLER_5 ,
FILLER_6 ,
SECTION ,
OLTAS_TDS_INCOME_TAX ,
OLTAS_TDS_SURCHARGE ,
OLTAS_TDS_CESS ,
OLTAS_TDS_INTEREST_AMT ,
OLTAS_TDS_OTHERS ,
TOTAL_DEPOSIT_AMT_CHLN ,
LAST_TOTAL_DEP_AMT ,
TOT_TAX_DEP_AMT ,
TDS_INCOME_TAX ,
TDS_SURCHARGE ,
TDS_CESS ,
SUM_TOT_IT_DEDUCTED_SOURCE ,
TDS_TCS_INTEREST_AMT ,
TDS_TCS_OTHERS_AMT ,
CHEQUE_DD_NO ,
BOOK_ENTRY_CASH ,
REMARKS ,
RECORD_HASH
)
INTO TABLE LAXMAN_ETDS_DD
WHEN RECORD_TYPE='DD'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NO ,
RECORD_TYPE ,
BATCH_NUMBER ,
CHLN_DET_RECORD ,
DEDUCTEE_DETAIL_RECORD_NO ,
MODE_ETDS ,
EMPLOYEE_SERIAL_NO ,
DEDUCTEE_CODE ,
LAST_PARTY_PAN ,
DEDUCTEE_PAN ,
LAST_PARTY_PAN_REF_NO ,
PAN_REF_NO ,
NAME_DEDUCTEE ,
TDS_IT_FOR_PERIOD ,
TDS_SURCHARGE ,
TDS_TCS_CESS ,
TOT_IT_DEDUC_SOURCE ,
LAST_TOT_IT_DEDUCT_SOURCE ,
TOT_TAX_DEPOSITED ,
LAST_TOT_TAX_DEPOSITED ,
TOT_PURCHASE ,
AMT_PAYMENT_CREDIT ,
DATE_AMT_PAID DATE( Cool "DDMMYYYY",
DATE_TAX_DEDUCTED DATE( Cool "DDMMYYYY",
DATE_DEPOSIT ,
RATE_TAX_DED_COLL ,
GROSSING_UP_IND ,
BOOK_ENTRY ,
DATE_TAX_DEDUCTION ,
REMARKS_1 ,
REMARKS_2 ,
REMARKS_3 ,
RECORD_HASH
)

The parfile is

control=loader.ctl
log=loader_log.log
bad=loader.bad
discard=discard.log
skip=0
rows=1
bindsize=100000
readsize=1000000
direct=N
discardmax=100000000
errors=1000000





  • Attachment: ABCD.txt
    (Size: 9.53KB, Downloaded 274 times)
Re: Loading data into multiple tables [message #201977 is a reply to message #201888] Tue, 07 November 2006 12:12 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
There is a pecuiliarity when you use multiple when clauses with a delimited data file. In order to get Oracle to read from the correct position, you need to add the starting position to the first column. So, you would need to change each "line_number," to "line_number position(1),".
Previous Topic: export schedule problem
Next Topic: Table Level Export
Goto Forum:
  


Current Time: Wed Dec 07 08:27:22 CST 2016

Total time taken to generate the page: 0.12462 seconds