Home » RDBMS Server » Server Utilities » oracle external table CHAR(255), cannot load value over 255 char
 oracle external table CHAR(255), cannot load value over 255 char [message #125195] |
Thu, 23 June 2005 14:32  |
dbconstructer
Messages: 11
Registered: April 2005
|
Junior Member |
|
|
Cannot load string longer than 255 from external table!!!
The external table I created is:
create table event_data (
id char(20),
device_id char(15),
event_id varchar2(255),
g_timestamp char(12),
user_id varchar2(100),
prioruser_id varchar2(100),
corr_count clob,
message clob
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY d_dml
ACCESS PARAMETERS
(
records delimited by newline skip 1
FIELDS TERMINATED BY x0'09'
MISSING FIELD VALUES ARE NULL
(id,device_id,event_id,g_timestamp,user_id,prioruser_id,corr_count,message)
)
LOCATION('load.dat')
)
PARALLEL 1
REJECT LIMIT 0;
If length of message greater than 255 the error message will be sent out:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
ORA-06512: at line 1
Then I checked the log file and found all the columns are set a fixed length CHAR(255), event for the clob column, message.
Fields in Data Source:
ID CHAR (255)
Terminated by "9"
Trim whitespace same as SQL Loader
DEVICE_ID CHAR (255)
Terminated by "9"
Trim whitespace same as SQL Loader
EVENT_ID CHAR (255)
Terminated by "9"
Trim whitespace same as SQL Loader
.....
MESSAGE CHAR (255)
Terminated by "9"
Trim whitespace same as SQL Loader
LOG file opened at 06/23/05 14:21:18
Field Definitions for table EVENT_DATA
Record format DELIMITED BY NEWLINE
How can I solve the problem.Thanks a millions.
|
|
|
|
|
|
| Re: oracle external table CHAR(255), cannot load value over 255 char [message #125210 is a reply to message #125209] |
Thu, 23 June 2005 16:37  |
dbconstructer
Messages: 11
Registered: April 2005
|
Junior Member |
|
|
Yes. I'm sure. What I did was reducing the length of the message value and selecting the table. The error was sent out untill the length was less than 255.
You know the problem is the way of oracle parse the table structure. I'm using 0x'09', a tab, as a delimiter. According to the log file, it seems all the columns' length is fixed as CHAR(255) event I defined the message as clob or varchar2(4000).
Thanks.
|
|
|
|
| Re: oracle external table CHAR(255), cannot load value over 255 char [message #125343 is a reply to message #125195] |
Fri, 24 June 2005 10:51 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Here is a create table:
MYDBA@ORCL > create table external_new_way
2 (
3 a number,
4 b date,
5 c varchar2(10)
6 )
7 organization external
8 (
9 type oracle_loader
10 default directory mydir
11 access parameters
12 (
13 records delimited by newline
14 badfile 'external_new_way.bad'
15 logfile 'external_new_way.log'
16 fields terminated by ','
17 (
18 a integer external,
19 b date mask 'DD-MON-YYYY',
20 c char
21 )
22 )
23 location ('external_new_way.dat')
24 )
25 reject limit 0
26 ;
Table created.
And here is a data file:
1,01-JAN-2005,aaaaaaaaaa
2,02-JAN-2005,bbbbbbbbbb
3,03-JAN-2005,cccccccccc
4,04-JAN-2005,dddddddddd
5,05-JAN-2005,eeeeeeeeee
6,44-JAN-2005,ffffffffff
Notice that the reject limit is 0, but the last line in the data file has an invalid date, which will cause the record to be rejected. When I select from the table, I get an error that looks very similar to your error above:
MYDBA@ORCL > select * from external_new_way;
select * from external_new_way
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
ORA-06512: at line 1
But if I were to change the data file to correct the problem, meaning the number of errors would be 0, which is less than or equal to the reject limit specified of 0, then I get:
MYDBA@ORCL > select * from external_new_way;
A B C
---------- -------------------- ----------
1 01-JAN-2005 00:00:00 aaaaaaaaaa
2 02-JAN-2005 00:00:00 bbbbbbbbbb
3 03-JAN-2005 00:00:00 cccccccccc
4 04-JAN-2005 00:00:00 dddddddddd
5 05-JAN-2005 00:00:00 eeeeeeeeee
6 06-JAN-2005 00:00:00 ffffffffff
6 rows selected.
Likewise, if I put the bad date back, but changed the reject limit to 1, then it would also "work" (assuming work meant rejecting up to 1 record was ok).
MYDBA@ORCL > create table external_new_way
2 (
3 a number,
4 b date,
5 c varchar2(10)
6 )
7 organization external
8 (
9 type oracle_loader
10 default directory mydir
11 access parameters
12 (
13 records delimited by newline
14 badfile 'external_new_way.bad'
15 logfile 'external_new_way.log'
16 fields terminated by ','
17 (
18 a integer external,
19 b date mask 'DD-MON-YYYY',
20 c char
21 )
22 )
23 location ('external_new_way.dat')
24 )
25 reject limit 1
26 ;
Table created.
MYDBA@ORCL > select * from external_new_way;
A B C
---------- -------------------- ----------
1 01-JAN-2005 00:00:00 aaaaaaaaaa
2 02-JAN-2005 00:00:00 bbbbbbbbbb
3 03-JAN-2005 00:00:00 cccccccccc
4 04-JAN-2005 00:00:00 dddddddddd
5 05-JAN-2005 00:00:00 eeeeeeeeee
5 rows selected.
|
|
|
|
|
|
|
|
| Re: oracle external table CHAR(255), cannot load value over 255 char [message #125368 is a reply to message #125195] |
Fri, 24 June 2005 14:08 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ok, I'm not sure if I'm following you, but perhaps this bit of the docs (and the rest of chapter 14) will help:
| Quote: |
datatype_spec
The datatype_spec clause indicates the datatype of the field. If datatype_spec is omitted, the access driver assumes the datatype is CHAR(255). For a full description of the syntax, see datatype_spec Clause.
datatype_spec Clause
The datatype_spec clause is used to describe the datatype of a field in the datafile if the datatype is different than the default. The datatype of the field can be different than the datatype of a corresponding column in the external table. The access driver handles the necessary conversions.
CHAR
The CHAR clause is used to indicate that a field is a character datatype. The length (len) for CHAR fields specifies the largest number of bytes or characters in the field. The len is in bytes or characters, depending on the setting of the STRING SIZES ARE IN clause.
If no length is specified for a field of datatype CHAR, then the size of the field is assumed to be 1, unless the field is delimited:
For a delimited CHAR field, if a length is specified, that length is used as a maximum.
For a delimited CHAR field for which no length is specified, the default is 255 bytes.
For a delimited CHAR field that is greater than 255 bytes, you must specify a maximum length. Otherwise you will receive an error stating that the field in the datafile exceeds maximum length.
|
And here is an example:
MYDBA@ORCL >
MYDBA@ORCL > create or replace directory mydir as 'e:\scot\sqlpath';
Directory created.
MYDBA@ORCL >
MYDBA@ORCL > create table long_chars
2 (
3 a number,
4 c varchar2(500)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory mydir
10 access parameters
11 (
12 records delimited by newline
13 badfile 'long_chars.bad'
14 logfile 'long_chars.log'
15 fields terminated by ','
16 (
17 a integer external,
18 c char(500)
19 )
20 )
21 location ('long_chars.dat')
22 )
23 reject limit 0
24 ;
Table created.
MYDBA@ORCL >
MYDBA@ORCL > select * from long_chars;
A
----------
C
-----------------------------------------------------------------------------------------------
1
xxx
2
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaa
2 rows selected.
MYDBA@ORCL >
MYDBA@ORCL > select length(c) from long_chars;
LENGTH(C)
----------
3
400
2 rows selected.
For the following data file:
1,xxx
2,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
[Updated on: Fri, 24 June 2005 14:12] Report message to a moderator |
|
|
|
| Re: oracle external table CHAR(255), cannot load value over 255 char [message #125670 is a reply to message #125195] |
Tue, 28 June 2005 10:04 |
dbconstructer
Messages: 11
Registered: April 2005
|
Junior Member |
|
|
Thank you very much for your example.
It works.
Still one more problem.
The column length defination cannot work with the following statement together:
MISSING FIELD VALUES ARE NULL
(a,c)
create table long_chars
(
a char(10),
c clob
)
organization external
(
type oracle_loader
default DIRECTORY d_dml
access parameters
(
records delimited by newline skip 1
badfile 'long_chars.bad'
logfile 'long_chars.log'
fields terminated by 0x'09'
(
a char(10),
c char(1500)
)
MISSING FIELD VALUES ARE NULL
(
a,c,exit
)
)
location ('long_chars.dat')
)
reject limit 0
;
select * from long_chars;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "missing": expecting one of: "column, exit"
KUP-01007: at line 9 column 16
ORA-06512: at "SYS.ORACLE_LOADER", line 19
ORA-06512: at line 1
no rows selected
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 24 22:12:54 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
