asked on

SQL-Loader

I am trying to load visa data into the oracle but it gives me following error

+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
/raid1/OACRPL/tmp/dopK0102.csv
------------

SQL*Loader: Release 8.0.6.3.0 - Production on Tue Oct 5 09:25:47 2004

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Control File: /u002/app/applmgr/OACRPLappl/ap/11.5.0/bin/apxusbv.ctl
Data File: /raid1/OACRPL/tmp/dopK0102.csv
Bad File: /u002/app/applmgr/OACRPLcomn/admin/out/OACRPL_nttdcdev01/o174170.out
Discard File: /u002/app/applmgr/OACRPLcomn/admin/out/OACRPL_nttdcdev01/o174170.out
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: 1:2 != 0X3035(character '05'), in next physical record
Path used: Conventional
Silent options: FEEDBACK and DISCARDS

Table "AP"."AP_CREDIT_CARD_TRXNS_ALL", loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TRX_ID FIRST 1 CHARACTER
CARD_NUMBER 3:18 16 CHARACTER
REFERENCE_NUMBER 19:41 23 CHARACTER
TRANSACTION_TYPE 42:45 4 CHARACTER
TRANSACTION_DATE 46:53 8 DATE YYYYMMDD
TRANSACTION_AMOUNT 66:76 11 CHARACTER
DEBIT_FLAG 65:65 1 CHARACTER
BILLED_DATE 46:53 8 DATE YYYYMMDD
BILLED_AMOUNT 81:91 11 CHARACTER
BILLED_CURRENCY_CODE 77:79 3 CHARACTER
POSTED_DATE 54:61 8 DATE YYYYMMDD
POSTED_AMOUNT 66:76 11 CHARACTER
POSTED_CURRENCY_CODE 62:64 3 CHARACTER
CURRENCY_CONVERSION_RATE 305:317 13 CHARACTER
SIC_CODE 92:95 4 CHARACTER
MERCHANT_TAX_ID 162:181 20 CHARACTER
MERCHANT_REFERENCE 274:298 25 CHARACTER
MERCHANT_NAME1 96:120 25 CHARACTER
MERCHANT_CITY 121:146 26 CHARACTER
MERCHANT_PROVINCE_STATE 147:149 3 CHARACTER
MERCHANT_POSTAL_CODE 153:161 9 CHARACTER
MERCHANT_COUNTRY 150:152 3 CHARACTER
LOCAL_TAX 236:247 12 CHARACTER
NATIONAL_TAX 249:260 12 CHARACTER
OTHER_TAX 261:272 12 CHARACTER

VALIDATE_CODE CONSTANT 'N'
PAYMENT_FLAG CONSTANT 'N'
CARD_PROGRAM_ID CONSTANT '1000'
EXPENSED_AMOUNT CONSTANT '0'
CREATION_DATE SYSDATE

Column TRX_ID had SQL string
"ap_credit_card_trxns_s1.nextval"
applied to it.
Column TRANSACTION_DATE is DEFAULT if TRANSACTION_DATE = BLANKS
Column TRANSACTION_AMOUNT had SQL string
"decode(:debit_flag,
'-', -1*abs(:transaction_amount/power(10,2)),
:transaction_amount/power(10,2))"
applied to it.
Column BILLED_DATE is DEFAULT if BILLED_DATE = BLANKS
Column BILLED_AMOUNT had SQL string
"decode(:debit_flag,
'-', -1*abs(:billed_amount),
:billed_amount)"
applied to it.
Column BILLED_CURRENCY_CODE had SQL string
"decode(:billed_currency_code,
'US', 'USD',
:billed_currency_code)"
applied to it.
Column POSTED_DATE is DEFAULT if POSTED_DATE = BLANKS
Column POSTED_AMOUNT had SQL string
"decode(:debit_flag,
'-', -1*abs(:posted_amount/power(10,2)),
:posted_amount/power(10,2))"
applied to it.
Column CURRENCY_CONVERSION_RATE had SQL string
":currency_conversion_rate/power(10,6)"
applied to it.

Record 1: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01843: not a valid month

Record 2: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01843: not a valid month

Record 3: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 4: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 5: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 6: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 7: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 8: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 9: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 10: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 11: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 12: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 13: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 14: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 15: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 16: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 17: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 18: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 19: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 20: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 21: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 22: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 23: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 24: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 25: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 26: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 27: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 28: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 29: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 30: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01847: day of month must be between 1 and last day of month

Record 31: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 32: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 33: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 34: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 35: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01843: not a valid month

Record 36: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 37: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 38: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column POSTED_DATE.
ORA-01843: not a valid month

Record 39: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 40: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 41: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 42: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 43: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 44: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 45: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 46: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 47: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 48: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 49: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 50: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

Record 51: Rejected - Error on table "AP"."AP_CREDIT_CARD_TRXNS_ALL", column TRANSACTION_DATE.
ORA-01843: not a valid month

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table "AP"."AP_CREDIT_CARD_TRXNS_ALL":
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 22208 bytes(64 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 64
Total logical records rejected: 51
Total logical records discarded: 0

Run began on Tue Oct 05 09:25:47 2004
Run ended on Tue Oct 05 09:25:49 2004

Elapsed time was: 00:00:01.89
CPU time was: 00:00:00.08

+---------------------------------------------------------------------------+

Executing request completion options...

Output is not being printed because:
The print option has been disabled for this report.

Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 05-OCT-2004 09:25:49

+---------------------------------------------------------------------------+

Here is my data...

0540000000000000107456445015131605545447110 2000061220000614840+00000116000840 000001160005251COMPUTER HERE WALNUT CREEK CA US 945960000 000000000000 000000000000000000000000 0000001000000
0540000000000000107456445015131605545447110 2000061220000615124+00000172500840 000001180005251NANTY ORDINATEUR ST-NICOLAS PQ CA G6J 1E8 000000000000 000000000000000000000000 0000000684057
0540000000000000107456445015131605545447110 2000061320000616250+00000300000840 000003000005734JACK SOFTWARE PARIS FR 0 000000000000 000000000000000000000000 0000001000000
0540000000000000107456445015131605545447110 2000061420000617840+00000050000840 000000500005734JACK SOFTWARE SAN FRANCISCO CA US 999480000 000000000000 000000000000000000000000 0000001000000
0540000000000000107456445015131605545447110 2000061520000618840+00000025000840 000000250005734JACK SOFTWARE SAN FRANCISCO CA US 999480000 000000000000 000000000000000000000000 0000001000000
0540000000000000111456445015131605545447110 2000061920000621840+00000116000840 000001160005947WE HAVE IT HERE WALNUT CREEK CA US 945960000 000000000000 000000000000000000000000 0000001000000
0540000000000000111456445015131605545447110 2000061920000622124+00000008000840 000000065005411PROVIGO ST-ETIENNE PQ CA G6J 1E5 000000000000 000000000000000000000000 0000000812500
0540000000000000111456445015131605545447110 2000062020000623250+00000300000840 000003000005399CAN FIND IT COME HERE PARIS FR 0 000000000000 000000000000000000000000 0000001000000
0540000000000000111456445015131605545447110 2000062120000624840+00000050000840 000000500005399CAN FIND IT COME HERE SAN FRANCISCO CA US 999480000 000000000000 000000000000000000000000 0000001000000
0540000000000000111456445015131605545447110 2000062220000625840+00000025000840 000000250005399CAN FIND IT COME HERE SAN FRANCISCO CA US 999480000 000000000000 000000000000000000000000 0000001000000
0540000000000000103456445015131605545447110 2000070520000705840+00000255600840 000002556001799ICC FOSTER CITY CA US 944040000 000000000000 000000000000000000000000 0000001000000
0540000000000000103456445015131605545447110 2000070520000705840+00000360000840 000003600001799ICC FOSTER CITY CA US 944040000 000000000000 000000000000000000000000 0000001000000
0540000000000000113456445015131605545447110 2000070520000705840+00000250000840 000002500001799TEMP HERE SAN MATEO CA US 944140000 000000000000 000000000000000000000000 0000001000000
0540000000000000113456445015131605545447110 2000070520000705840+00000301000840 000003010001799TEMP HERE SAN MATEO CA US 944140000 000000000000 000000000000000000000000 0000001000000
0540000000000001003456445015131605545447110 2000070420000705840+00000015000840 000000150004215FEDEX MEMPHIS TN US 944040000 000000000000 000000000000000000000000 0000001000000
0540000000000001003456445015131605545447110 2000070620000707840+00000015000840 000000150004215FEDEX MEMPHIS TN US 944040000

there is more but i copied for example.

it gives me error in date field.

frinpd

ASKER

Here is the CTL file

-- | $Header: apxusbv.ctl 115.0 2000/05/10 11:50:53 pkm ship $|
-- +==================================================================+
-- | Copyright (c) 1999 Oracle Corporation
-- | Redwood Shores, California, USA
-- | All rights reserved.
-- +==================================================================+
-- | Name:
-- | apxusbv.ctl (SQL*Loader Control file)
-- |
-- | Description:
-- | US Bank Visa Transaction Loader
-- | This control file specifies the format of loading US Bank
-- | Visa transaction records
-- |
-- | Assumptions:
-- | Valid transaction records begin with:
-- | '05' - Transaction record (length=400)
-- | '??' - ATM
-- | '??' - Restaurant
-- | '30' - Car Rental
-- | '20' - Hotel
-- | '10' - Airline
-- | Record length = 400
-- |
-- | Setup:
-- | Please replace CARD_PROGRAM_ID with the valid card_program_id of the
-- | Card Program you will be loading these transactions for.
-- |
-- | History:
-- | 18-MAY-99 Ron Langi Created
-- |
-- +==================================================================+
options (silent=(header,feedback,discards))
load data
infile *
append

continueif next(1:2) <> '05'

-- ALL TRANSACTIONS

into table ap_credit_card_trxns_all
--when (1:2) = '05'
(
trx_id                        "ap_credit_card_trxns_s1.nextval",
validate_code                  constant 'N',
payment_flag constant 'N',
card_program_id            constant '1000',
expensed_amount            constant 0,
card_number                  position(3:18)      char,
reference_number            position(19:41)            char,
--record_type
--merchant_activity
transaction_type            position(42:45)            char,
--financial_category
transaction_date            position(46:53)            date "YYYYMMDD"
      defaultif(transaction_date=blanks),
transaction_amount            position(66:76)            decimal external
      "decode(:debit_flag,
            '-', -1*abs(:transaction_amount/power(10,2)),
            :transaction_amount/power(10,2))",
debit_flag                  position(65:65)            char,
billed_date                  position(46:53)            date "YYYYMMDD"
      defaultif(billed_date=blanks),
billed_amount                  position(81:91)            decimal external
      "decode(:debit_flag,
            '-', -1*abs(:billed_amount),
            :billed_amount)",
--billed_decimal
billed_currency_code            position(77:79)            char
      "decode(:billed_currency_code,
'US', 'USD',
:billed_currency_code)",
posted_date                  position(54:61)            date "YYYYMMDD"
      defaultif(posted_date=blanks),
posted_amount                  position(66:76)            decimal external
      "decode(:debit_flag,
            '-', -1*abs(:posted_amount/power(10,2)),
            :posted_amount/power(10,2))",
--posted_decimal
posted_currency_code            position(62:64)            char,
--currency_conversion_exponent
currency_conversion_rate      position(305:317)      decimal external
      ":currency_conversion_rate/power(10,6)",
--mis_industry_code
sic_code                  position(92:95)            integer external,
merchant_tax_id            position(162:181)      char,
merchant_reference            position(274:298)      char,
merchant_name1                  position(96:120)      char,
--merchant_name2
--merchant_address1
--merchant_address2
--merchant_address3
--merchant_address4
merchant_city                  position(121:146)      char,
merchant_province_state      position(147:149)      char,
merchant_postal_code            position(153:161)      char,
merchant_country            position(150:152)      char,
--total_tax
local_tax                  position(236:247)      decimal external,
national_tax                  position(249:260)      decimal external,
other_tax                  position(261:272)      decimal external,
--org_id
--last_update_date
--last_updated_by
--last_update_login
creation_date                  sysdate
--created_by
)

konektor

probably u have wrong format of DATE values in your file. change them of put format mask to create tabel clausule, eg.

CREATE TABLE ... (
DomicileCountry VARCHAR2(2),
BirthDate DATE
)
organization external(
type oracle_loader
default directory ...
access parameters( records delimited by newline characterset ...
skip 0
badfile ...:'....bad'
logfile ...:....log'
discardfile ...:'ExtPersonalFO.discard'
fields terminated by ';'
enclosed by '"'
(
DomicileCountry CHAR(2),
BirthDate DATE "DDMMYYYY"
) location( ...:'....csv'))
/

seazodiac

frinpd:

can you open the source csv data file in EXCEL and copy and paste the COLUMN#11 (the 11th column from the beginning ) (which is the posted_date column).?

what caused this errror is that you have the data in the POSTED_DATE column(column11 in the csv file) having the DATE format different from what you specified "YYYYMMDD'...

frinpd

ASKER

I couldn't get you. where you want me to change this format ? ctl file ? as you know in data file date format is yyyymmdd

frinpd

ASKER

2000061220000614 this is date respectivelly for billed_date and transaction_date

Ora_Techie

Yes you will change the date format to yyyymmdd in the ctl file

seazodiac

can you change this line in your control file like this and test?

posted_date position(54:61) date to_date(:posted_date, 'YYYYMMDD'),

oh, also for the transaction_date

frinpd

ASKER

seazodic,

i did the above change and run again i got the following error. also i wanted to note that i opened TAR for the same and they says that the value return from data file for 46:53 will be 0000612 and not 20000612 b'caz when you specify 46 it will start from 47.. i don't think so this is true.

+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
/raid1/OACRPL/tmp/dopK0102.csv
------------
SQL*Loader-350: Syntax error at line 57.
Expecting valid column specification, "," or ")", found "to_date".
transaction_date position(46:53) date to_date(:transaction_date,'YY
^

SQL*Loader: Release 8.0.6.3.0 - Production on Tue Oct 5 11:14:33 2004

(c) Copyright 1999 Oracle Corporation. All rights reserved.

SQL*Loader-350: Syntax error at line 57.
Expecting valid column specification, "," or ")", found "to_date".
transaction_date position(46:53) date to_date(:transaction_date,'YY
^

Program exited with status 1
Concurrent Manager encountered an error while running SQL*Loader for your concurrent request 174175.

Review your concurrent request log file for more detailed information.

+---------------------------------------------------------------------------+

Executing request completion options...

Output is not being printed because:
The print option has been disabled for this report.

Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 05-OCT-2004 11:14:34

+---------------------------------------------------------------------------+

seazodiac

that's true, unfortunately.

the problem with specifying the position, is that you have to be very accurate. otherwise, you got invalid data.

to_date function actually kind of did acheck for you....

Ora_Techie

No seazodiac, i beg to differ here. The Actual problem (according to my thinking) is that the tto_date(:transaction_date,'YYyymmdd') should be in double quotes. So the ctl file should look like this:

transaction_date position(46:53) date "to_date(:transaction_date,'YYYYmmdd')"

OR simply

transaction_date position(46:53) "to_date(:transaction_date,'YYYYmmdd')"

frinpd

ASKER

Thanks fox for your suggession.. but i solved the problems. The positioning is wrong in data file. and when u specify 46 it starts from 47 so in order to capture value of position 46 one need to specify 45. that is what Oracle people suggested.

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial