I am importing data from an informix db to create a new Oracle DB. One of my tables has a column containing the characters ^M/, which is used to identify a new line for that column display in informix and is escaped with the /. How do I get this to load into an Oracle table? trying to load to an external table then inserting into table. Below is the code used:
DROP TABLE ardb.commsite_inventory_review_ext;
CREATE TABLE ardb.commsite_inventory_review_ext
(review_id NUMBER (10),
inventory_id NUMBER (10),
serial_nr_full VARCHAR2 (17),
reviewed_by VARCHAR2 (255),
notes VARCHAR2 (4000),
created_by VARCHAR2 (20),
created_date DATE,
modified_by VARCHAR2 (20),
modified_date DATE
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY newline
badfile load_dir_bad_commsite_inventory_review
logfile load_dir_log_commsite_inventory_review
fields terminated by '|'
missing field values are null
(review_id, inventory_id, serial_nr_full, reviewed_by, notes varchar(4000),
created_by, created_date date mask 'yyyy-mm-dd hh24:mi:ss',
modified_by, modified_date date mask 'yyyy-mm-dd hh24:mi:ss'
)
)
LOCATION ('commsite_inventory_review.tmp')
-- LOCATION ('commsite_inventory_review.load')
)
-- only use for large tables
-- PARALLEL
REJECT LIMIT UNLIMITED;
-- enable parallel for loading (good if lots of data to load)
--ALTER SESSION ENABLE PARALLEL DML;
truncate table ardb.commsite_inventory_review;
select count(*) from ardb.commsite_inventory_review;
select count(*) from ardb.commsite_inventory_review_ext;
insert into ardb.commsite_inventory_review select * from ardb.commsite_inventory_review_ext;
Here are the errors I am receiving,
KUP-04021: field formatting error for field NOTES
KUP-04026: field too long for datatype
KUP-04101: record 1 rejected in file /u01/oracle/imports/csrcd/commsite_inventory_review.load
I am receiving these 3 errors for the many times that these characters are included in this column for the many rows for this table. Can anyone help with suggestions on how to get this loaded?
Thanks, Ron