Solved

SQL-Loader

Posted on 2004-10-05
12
8,882 Views
Last Modified: 2008-01-09
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.

0
Comment
Question by:frinpd
  • 5
  • 4
  • 2
  • +1
12 Comments
 

Author Comment

by:frinpd
ID: 12226805
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
)



0
 
LVL 9

Expert Comment

by:konektor
ID: 12226839
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'))
/
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12226909
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'...
0
 

Author Comment

by:frinpd
ID: 12226927
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
0
 

Author Comment

by:frinpd
ID: 12226947
2000061220000614 this is date respectivelly for billed_date and transaction_date
0
 
LVL 13

Expert Comment

by:riazpk
ID: 12226994
Yes you will change the date format to yyyymmdd in the ctl file
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 12227014
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
0
 

Author Comment

by:frinpd
ID: 12227598
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

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

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12227622
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....
0
 
LVL 13

Expert Comment

by:riazpk
ID: 12257165
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')"

0
 

Author Comment

by:frinpd
ID: 12261907
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.
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 12262015
frinpd:

Like I said, when you specifiy positions, you have to be very accurate, or your data is messed up.

Another efficient way to do this is :

using  this line into your ctl file:

fields terminated by WHITESPACE


this will break each fields for you instead of you going after each field with position numbers.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now