Help , I'm just trying to load a .csv into an oracle database.
data looks like this:
175964609,000641402D,NSP,0001511
.ctl looks like this:
fields terminated by ','
(vpur_transaction_id ,
SBI ,
AGY_LOC_ID,
Purchase_amount ,
Purchase_date sysdate)
database table looks like this:
transaction_id varchar2(10)
SBI varchar2(10) ,
AGY_LOC_ID varchar2(4),
Purchase_amount number(7,2)
Purchase_date date
Error Message: Record 1: Rejected - Error on table OMS_OWNER.NJ_VPAY_PURCHASES, column PURCHASE_AMOUNT.
ORA-01722: invalid number
I'm trying to get the purchase amount to be 15.11 in the table, but at this point i don't understand why none of my tries are working I've tried:
Purchase_amount ,
Purchase_amount integer,
Purchase_amount "to_number(:PURCHASE_AMOUNT,'0000000')/100",
Purchase_amount char "to_number(PURCHASE_AMOUNT,'99999.99')"
Purchase_amount integer "to_number(PURCHASE_AMOUNT,'99999.99')"
Purchase_amount ":PURCHASE_AMOUNT/100",
Purchase_amount integer ":PURCHASE_AMOUNT/100",
Purchase_amount "to_number(:PURCHASE_AMOUNT,'99999D99')",
I've even tried changing the data to 175964609,000641402D,NSP,00015.11
Purchase_amount decimal external,
nothing works errors on purchase amount every time