Solved

SQL-Loader

Posted on 2004-10-05
12
8,991 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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
 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

688 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