Home » SQL & PL/SQL » SQL & PL/SQL » External Tables (Oracle Loader) (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: XP Window )
External Tables (Oracle Loader) [message #577308] Thu, 14 February 2013 03:21 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

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 35 times)
Re: External Tables (Oracle Loader) [message #577312 is a reply to message #577308] Thu, 14 February 2013 03:31 Go to previous messageGo to next message
Littlefoot
Messages: 19345
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This CSV file doesn't raise that error.
Re: External Tables (Oracle Loader) [message #577317 is a reply to message #577312] Thu, 14 February 2013 03:36 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Sad
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 #577318 is a reply to message #577317] Thu, 14 February 2013 03:37 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Actually out of 3 rows , last row is getting generating whereas for 1st two rows its throwing the error in bad file also.
Re: External Tables (Oracle Loader) [message #577320 is a reply to message #577317] Thu, 14 February 2013 03:38 Go to previous messageGo to next message
Littlefoot
Messages: 19345
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not saying that YOUR CSV file doesn't produce the error. I said that CSV file you attached to your first message won't produce it.
SQL> select prc_pol_no, prc_rec_type
  2  from et_pgit_pol_risk_cover;

PRC_POL_NO                                                   P
------------------------------------------------------------ -
CHANDAN                                                      N
CHANDAN                                                      N
CHANDAN                                                      N

SQL>
Re: External Tables (Oracle Loader) [message #577321 is a reply to message #577320] Thu, 14 February 2013 03:43 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

How same csv file is working in your side and not working in my side. Did u change anything ? I mean you used same syntax for creating the External table ?
Re: External Tables (Oracle Loader) [message #577324 is a reply to message #577321] Thu, 14 February 2013 03:45 Go to previous messageGo to next message
Littlefoot
Messages: 19345
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 #577325 is a reply to message #577321] Thu, 14 February 2013 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure the database is on Windows?

Regards
Michel
Re: External Tables (Oracle Loader) [message #577326 is a reply to message #577325] Thu, 14 February 2013 03:55 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Sad Sorry Database is on linux server.
Re: External Tables (Oracle Loader) [message #577327 is a reply to message #577326] Thu, 14 February 2013 04:05 Go to previous messageGo to next message
Littlefoot
Messages: 19345
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Then you might need to modify line terminator. Linux uses "newline" (\n) while Windows use "carriage return + newline" characters (\r\n).
Re: External Tables (Oracle Loader) [message #577328 is a reply to message #577327] Thu, 14 February 2013 04:07 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Where i need to modify for newline ?
Re: External Tables (Oracle Loader) [message #577329 is a reply to message #577328] Thu, 14 February 2013 04:19 Go to previous messageGo to next message
Littlefoot
Messages: 19345
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, in
records delimited by
.
Re: External Tables (Oracle Loader) [message #577330 is a reply to message #577328] Thu, 14 February 2013 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or you can modify the file using "dos2unix" program or the like (search on the web).
Next time, don't forget to transfer from Windows to Linux in TEXT mode and not in BINARY mode.

Regards
Michel
Re: External Tables (Oracle Loader) [message #577331 is a reply to message #577330] Thu, 14 February 2013 04:39 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Micheal i actually using the FileZilla software to transfer the files from Windows into Linux.There transfer mode is Auto but i think its getting transferred in ASCII mode not Binary Mode.
Re: External Tables (Oracle Loader) [message #577332 is a reply to message #577331] Thu, 14 February 2013 04:40 Go to previous messageGo to next message
Littlefoot
Messages: 19345
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe you should "make sure" (instead of "think") because - it doesn't work correctly, does it?
Re: External Tables (Oracle Loader) [message #577333 is a reply to message #577331] Thu, 14 February 2013 04:41 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

@Littlefoot i have used below only.
RECORDS DELIMITED BY NEWLINE


Anywhere somewhere i need to change ?
Re: External Tables (Oracle Loader) [message #577335 is a reply to message #577332] Thu, 14 February 2013 04:43 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Razz Yes Correct and Now i am 100% sure its ASCII only Smile as i changed it and transferred but problem is same only.
Re: External Tables (Oracle Loader) [message #577337 is a reply to message #577335] Thu, 14 February 2013 04:51 Go to previous messageGo to next message
Littlefoot
Messages: 19345
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would
RECORDS DELIMITED BY '\r\n'
do any good?
Re: External Tables (Oracle Loader) [message #577356 is a reply to message #577337] Thu, 14 February 2013 07:10 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Thanks its worked Smile
Re: External Tables (Oracle Loader) [message #577357 is a reply to message #577356] Thu, 14 February 2013 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This proves that despite your opinion you didn't transfer in ascii mode... or your tool is bugged.
Why not using the good old "ftp" or "sftp" or "Winscp"? All of them are reliable.

Regards
Michel
Re: External Tables (Oracle Loader) [message #577358 is a reply to message #577357] Thu, 14 February 2013 07:54 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

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 #577361 is a reply to message #577358] Thu, 14 February 2013 08:15 Go to previous messageGo to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
WinSCP is as user friendly as Fillezilla. Drag, drop and that's it.

http://i46.tinypic.com/2583woo.png

Steve!
Re: External Tables (Oracle Loader) [message #577362 is a reply to message #577361] Thu, 14 February 2013 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And in "Options" menu you can name the extensions that have to be transfer in ASCII mode.
Most of these tools have BINARY mode by default, if this is the first time you use Filezilla, maybe it is not configured to transfer csv files in ASCII mode.

Regards
Michel
Re: External Tables (Oracle Loader) [message #577384 is a reply to message #577362] Thu, 14 February 2013 12:03 Go to previous message
Littlefoot
Messages: 19345
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.
Previous Topic: Find Out first thursday for each month
Next Topic: SQL Converting a varchar to a clob within an SQL union
Goto Forum:
  


Current Time: Thu Jul 31 15:45:39 CDT 2014

Total time taken to generate the page: 0.10723 seconds