| External Tables (Oracle Loader) [message #577308] |
Thu, 14 February 2013 03:21  |
|
|
Hi While creating the external Table, i am getting error.
ORA-12899: value too large for column PRC_REC_TYPE (actual: 2, maximum: 1)
But while checking the CSV file, i found that prc_rec_type is having one 1 length value.
I am uploading the csv file also.Kindly check it.
-- Create table
create table ET_PGIT_POL_RISK_COVER
(
prc_pol_no VARCHAR2(60),
prc_end_no_idx VARCHAR2(22),
prc_sec_code VARCHAR2(12),
prc_risk_id VARCHAR2(12),
prc_smi_code VARCHAR2(12),
prc_code VARCHAR2(12),
prc_desc VARCHAR2(2000),
prc_rate VARCHAR2(22),
prc_rate_per VARCHAR2(22),
prc_cvr_type VARCHAR2(1),
prc_add_si_yn VARCHAR2(1),
prc_si_curr_code VARCHAR2(12),
prc_prem_curr_code VARCHAR2(12),
prc_si_fc VARCHAR2(22),
prc_si_lc_1 VARCHAR2(22),
prc_si_lc_2 VARCHAR2(22),
prc_si_lc_3 VARCHAR2(22),
prc_prem_fc VARCHAR2(22),
prc_prem_lc_1 VARCHAR2(22),
prc_prem_lc_2 VARCHAR2(22),
prc_prem_lc_3 VARCHAR2(22),
prc_eff_fm_dt VARCHAR2(30),
prc_eff_to_dt VARCHAR2(30),
prc_brok_comm_appl_yn VARCHAR2(1),
prc_no_clm_bonus_appl_yn VARCHAR2(1),
prc_prof_comm_appl_yn VARCHAR2(1),
prc_cr_uid VARCHAR2(12),
prc_rate_eft VARCHAR2(12),
prc_terrorism_yn VARCHAR2(1),
prc_lvl VARCHAR2(12),
prc_tot_si_fc VARCHAR2(22),
prc_first_loss_perc VARCHAR2(22),
prc_flxi_risk_cover_desc VARCHAR2(2000),
prc_rec_type VARCHAR2(1)
)
organization external
(
type ORACLE_LOADER
default directory MMI_DATA_MIG
access parameters
(
RECORDS DELIMITED BY NEWLINE BADFILE 'ET_PGIT_POL_RISK_COVER.Bad' LOGFILE 'ET_PGIT_POL_RISK_COVER.Log' FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
)
location (MMI_DATA_MIG:'COVER_1.csv')
)
reject limit UNLIMITED;
-
Attachment: COVER_1.csv
(Size: 0.62KB, Downloaded 12 times)
|
|
|
|
|
|
| Re: External Tables (Oracle Loader) [message #577317 is a reply to message #577312] |
Thu, 14 February 2013 03:36  |
|
|
So what log file is saying after generating ? For me its throwing the error
Quote:
error processing column PRC_REC_TYPE in row 1 for datafile /data/mmi_mig_data/COVER_1.csv
ORA-12899: value too large for column PRC_REC_TYPE (actual: 2, maximum: 1)
error processing column PRC_REC_TYPE in row 2 for datafile /data/mmi_mig_data/COVER_1.csv
ORA-12899: value too large for column PRC_REC_TYPE (actual: 2, maximum: 1)
|
|
|
|
|
|
|
|
|
|
| Re: External Tables (Oracle Loader) [message #577324 is a reply to message #577321] |
Thu, 14 February 2013 03:45 |
 |
Littlefoot
Messages: 17245
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I changed only the directory name, nothing else. Tested on 10.2.0.3.0.
SQL> create table et_pgit_pol_risk_cover
2 (
3 prc_pol_no varchar2 (60),
4 prc_end_no_idx varchar2 (22),
5 prc_sec_code varchar2 (12),
6 prc_risk_id varchar2 (12),
7 prc_smi_code varchar2 (12),
8 prc_code varchar2 (12),
9 prc_desc varchar2 (2000),
10 prc_rate varchar2 (22),
11 prc_rate_per varchar2 (22),
12 prc_cvr_type varchar2 (1),
13 prc_add_si_yn varchar2 (1),
14 prc_si_curr_code varchar2 (12),
15 prc_prem_curr_code varchar2 (12),
16 prc_si_fc varchar2 (22),
17 prc_si_lc_1 varchar2 (22),
18 prc_si_lc_2 varchar2 (22),
19 prc_si_lc_3 varchar2 (22),
20 prc_prem_fc varchar2 (22),
21 prc_prem_lc_1 varchar2 (22),
22 prc_prem_lc_2 varchar2 (22),
23 prc_prem_lc_3 varchar2 (22),
24 prc_eff_fm_dt varchar2 (30),
25 prc_eff_to_dt varchar2 (30),
26 prc_brok_comm_appl_yn varchar2 (1),
27 prc_no_clm_bonus_appl_yn varchar2 (1),
28 prc_prof_comm_appl_yn varchar2 (1),
29 prc_cr_uid varchar2 (12),
30 prc_rate_eft varchar2 (12),
31 prc_terrorism_yn varchar2 (1),
32 prc_lvl varchar2 (12),
33 prc_tot_si_fc varchar2 (22),
34 prc_first_loss_perc varchar2 (22),
35 prc_flxi_risk_cover_desc varchar2 (2000),
36 prc_rec_type varchar2 (1)
37 )
38 organization external
39 (
40 type oracle_loader
41 default directory ext_dir
42 access parameters
43 (
44 records delimited by newline badfile 'ET_PGIT_POL_RISK_COVER.Bad'
45 logfile 'ET_PGIT_POL_RISK_COVER.Log'
46 fields terminated by ','
47 missing field values are null
48 reject rows with all null fields
49 )
50 location (ext_dir:'COVER_1.csv')
51 )
52 reject limit unlimited;
Table created.
SQL> select prc_code, prc_desc, prc_rec_type from et_pgit_pol_risk_cover;
PRC_CODE PRC_DESC P
------------ ------------------------------ -
1004 TP Property Damage N
1005 Road Side Assistance N
1099 SASRIA N
SQL> [Updated on: Thu, 14 February 2013 03:46] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Tables (Oracle Loader) [message #577358 is a reply to message #577357] |
Thu, 14 February 2013 07:54 |
|
|
|
I did transfer using ASCII mode only.May tool is having some problem but it is used by some of my team mate.I am using it first time.dont know about "sftp" or "Winscp" but ftp i have used to compile forms files.Filezilla looks to be more user friendly as we dont have to write the commands to move to it.Drag or double click will move the files.Now i will try to move the files using ftp in future.
|
|
|
|
|
|
|
|
| Re: External Tables (Oracle Loader) [message #577384 is a reply to message #577362] |
Thu, 14 February 2013 12:03 |
|
Littlefoot
Messages: 17245
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
|
That is quite possible (I mean, that binary mode was used by default with CSV files) because - if you don't do anything but simply double-click a CSV file on MS Windows, Excel will be used to open it. Excel files are binary files, so - in turn - Filezilla used binary mode.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
Yes Correct and Now i am 100% sure its ASCII only 
as i changed it and transferred but problem is same only. 
