| EXTERNAL TABLE [message #609124] |
Mon, 03 March 2014 02:25  |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
Dear All,
I am using an external table to loading a *.csv file on a daily basis. It's working fine if the data type is characters. If the data is of integer the value doesn't get updated properly, the - sign is not getting loaded. Kindly guide me on this.
CREATE TABLE autops_rms_ledger_ext(
arl_client_id varchar2(100),
arl_cash number(18,3),
arl_adhoc number(18,3)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY WES_DIR
ACCESS PARAMETERS
(FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
arl_client_id char,
arl_cash integer,
arl_adhoc integer
))
LOCATION ('auto_rms.csv'))
PARALLEL
REJECT LIMIT 0
-
Attachment: auto_rms.csv
(Size: 0.25KB, Downloaded 2151 times)
|
|
|
|
|
|
|
|
| Re: EXTERNAL TABLE [message #609127 is a reply to message #609126] |
Mon, 03 March 2014 02:30  |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
Just when I query the data in the external table, the data is improper
select * from autops_rms_ledger_ext
1 935178 926495277.000 775108665.000
2 14164 909718061.000 775042357.000
3 98005 943010093.000 775434804.000
4 23010 825766189.000 775238966.000
5 746324 943075629.000 775042866.000
6 130270 842609709.000 808334391.000
7 483012 809054765.000 775435828.000
8 798694 942879533.000 909325877.000
9 1022471 942748205.000 909325108.000
10 922122 808463920.000 808530220.000
|
|
|
|
|
|
| Re: EXTERNAL TABLE [message #609131 is a reply to message #609128] |
Mon, 03 March 2014 02:42 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
... or "nothing" (i.e. rewrite the external table as follows):
SQL> CREATE TABLE autops_rms_ledger_ext
2 (
3 arl_client_id VARCHAR2 (100),
4 arl_cash NUMBER (18, 3),
5 arl_adhoc NUMBER (18, 3)
6 )
7 ORGANIZATION EXTERNAL
8 (TYPE oracle_loader
9 DEFAULT DIRECTORY WES_DIR
10 ACCESS PARAMETERS
11 (FIELDS TERMINATED BY ','
12 MISSING FIELD VALUES ARE NULL
13 )
14 LOCATION ('auto_rms.csv'))
15 PARALLEL
16 REJECT LIMIT 0;
Table created.
SQL> SELECT * FROM autops_rms_ledger_ext;
ARL_CLIENT_ID ARL_CASH ARL_ADHOC
--------------- ---------- ----------
935178 -297983 0
14164 -296552 0
98005 -158428 0
23010 -181655 0
746324 -168272 0
130270 -49274 0
483012 -290468 0
798694 -3385636 7070884
1022471 -2184336 2540425
922122 0 1100000
10 rows selected.
SQL>[Updated on: Mon, 03 March 2014 02:43] Report message to a moderator |
|
|
|
| Re: EXTERNAL TABLE [message #609132 is a reply to message #609128] |
Mon, 03 March 2014 02:42 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> CREATE TABLE autops_rms_ledger_ext(
2 arl_client_id varchar2(100),
3 arl_cash number(18,3),
4 arl_adhoc number(18,3)
5 )
6 ORGANIZATION EXTERNAL
7 (TYPE oracle_loader
8 DEFAULT DIRECTORY c_dir
9 ACCESS PARAMETERS
10 (FIELDS TERMINATED BY ','
11 MISSING FIELD VALUES ARE NULL
12 (
13 arl_client_id char,
14 arl_cash integer,
15 arl_adhoc integer
16 ))
17 LOCATION ('auto_rms.csv'))
18 PARALLEL
19 REJECT LIMIT 0
20 /
Table created.
SQL> select * from autops_rms_ledger_ext ;
ARL_CLIENT_ID ARL_CASH ARL_ADHOC
--------------- ---------- ----------
935178 926495277 775108665
14164 909718061 775042357
98005 943010093 775434804
23010 825766189 775238966
746324 943075629 775042866
130270 842609709 808334391
483012 809054765 775435828
798694 942879533 909325877
1022471 942748205 909325108
922122 808463920 808530220
SQL> drop table autops_rms_ledger_ext;
Table dropped.
SQL> CREATE TABLE autops_rms_ledger_ext(
2 arl_client_id varchar2(100),
3 arl_cash number(18,3),
4 arl_adhoc number(18,3)
5 )
6 ORGANIZATION EXTERNAL
7 (TYPE oracle_loader
8 DEFAULT DIRECTORY c_dir
9 ACCESS PARAMETERS
10 (FIELDS TERMINATED BY ','
11 MISSING FIELD VALUES ARE NULL
12 (
13 arl_client_id char,
14 arl_cash char,
15 arl_adhoc char
16 ))
17 LOCATION ('auto_rms.csv'))
18 PARALLEL
19 REJECT LIMIT 0
20 /
Table created.
SQL> select * from autops_rms_ledger_ext ;
ARL_CLIENT_ID ARL_CASH ARL_ADHOC
--------------- ---------- ----------
935178 -297983 0
14164 -296552 0
98005 -158428 0
23010 -181655 0
746324 -168272 0
130270 -49274 0
483012 -290468 0
798694 -3385636 7070884
1022471 -2184336 2540425
922122 0 1100000
|
|
|
|
| Re: EXTERNAL TABLE [message #609136 is a reply to message #609125] |
Mon, 03 March 2014 03:53 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
I tried with the 2 above mentioned statements, it's executing but while querying the external table I am getting the below mentioned error.
SQL> select * from autops_rms_ledger_ext ;
Error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
|
|
|
|
|
|
|
|
|
|
| Re: EXTERNAL TABLE [message #609153 is a reply to message #609151] |
Mon, 03 March 2014 04:36 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
CREATE TABLE autops_rms_ledger_ext(
arl_client_id varchar2(100),
arl_cash number(18,3),
arl_adhoc number(18,3)
)
csv file is attached in the first post itself
|
|
|
|
|
|
|
|
| Re: EXTERNAL TABLE [message #609158 is a reply to message #609155] |
Mon, 03 March 2014 04:44 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
select * from v$version;
BANNER
1 Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
2 PL/SQL Release 10.2.0.4.0 - Production
3 CORE 10.2.0.4.0 Production
4 TNS for Linux: Version 10.2.0.4.0 - Production
5 NLSRTL Version 10.2.0.4.0 - Production
select value from V$NLS_PARAMETERS where PARAMETER='NLS_NUMERIC_CHARACTERS';
VALUE
1 .,
|
|
|
|
| Re: EXTERNAL TABLE [message #609196 is a reply to message #609158] |
Mon, 03 March 2014 14:56 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
What about the create table statement which has been requested?
I tried in a 10.2.0.1.0 database with Littlefoot's effort and it worked for me.
SQL> l
1 CREATE TABLE autops_rms_ledger_ext
2 (
3 arl_client_id VARCHAR2 (100),
4 arl_cash NUMBER (18, 3),
5 arl_adhoc NUMBER (18, 3)
6 )
7 ORGANIZATION EXTERNAL
8 (TYPE oracle_loader
9 DEFAULT DIRECTORY MOD_SOURCE
10 ACCESS PARAMETERS
11 (FIELDS TERMINATED BY ','
12 MISSING FIELD VALUES ARE NULL
13 )
14 LOCATION ('auto_rms.csv'))
15 PARALLEL
16* REJECT LIMIT 0
SQL> /
Table created.
SQL> select * from autops_rms_ledger_ext;
ARL_CLIENT_ID ARL_CASH ARL_ADHOC
-------------------- ---------- ----------
935178 -297983 0
14164 -296552 0
98005 -158428 0
23010 -181655 0
746324 -168272 0
130270 -49274 0
483012 -290468 0
798694 -3385636 7070884
1022471 -2184336 2540425
922122 0 1100000
10 rows selected.
I have a feeling you do not know the difference between a table and an external table or have faked the names since you show an external table and a regular table with the same name.
[Updated on: Mon, 03 March 2014 15:01] Report message to a moderator |
|
|
|
| Re: EXTERNAL TABLE [message #609207 is a reply to message #609196] |
Tue, 04 March 2014 00:23 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
External Table Name:autops_rms_ledger_ext
CREATE TABLE autops_rms_ledger_ext
(
arl_client_id VARCHAR2 (100),
arl_cash NUMBER (18, 3),
arl_adhoc NUMBER (18, 3)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY wes_dir
ACCESS PARAMETERS
(
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION ('auto_rms.csv')
)
PARALLEL
REJECT LIMIT 0
After creating the external table autops_rms_ledger_ext, when I query that table I am getting the below mentioned error. I am also attaching the error log file.
SQL> select * from autops_rms_ledger_ext ;
Error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
Only after successful creation I will push the data into a regular table.
Regular Table Name: autops_rms_ledger_org
CREATE TABLE autops_rms_ledger_org(
arl_client_id varchar2(100),
arl_cash number(18,3),
arl_adhoc number(18,3)
)
insert into autops_rms_ledger_org select * from autops_rms_ledger_ext
|
|
|
|
| Re: EXTERNAL TABLE [message #609210 is a reply to message #609207] |
Tue, 04 March 2014 00:50 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Log file says:
error processing column ARL_ADHOC in row 1 for datafile /cdsloutput/auto_rms.csv
ORA-01722: invalid number
which means that you aren't selecting data you provided to us (i.e. it is not a number but something else.
Oracle
ORA-01722: invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
Either fix it or provide real file you are trying to load so that someone could see what's going on.
P.S. Forgot to mention: NLS_NUMERIC_CHARACTERS are ".,". Switch them and then try again. How? By altering session:alter session set nls_numeric_characters = ',.'; [Updated on: Tue, 04 March 2014 00:53] Report message to a moderator |
|
|
|
| Re: EXTERNAL TABLE [message #609212 is a reply to message #609210] |
Tue, 04 March 2014 01:00 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
I have attached the same auto_rms.csv file. Actually this same csv file is woking fine, when I try to load through sqlldr, but when I try to import using external table concept I am getting the said error.
Sql Loader (works fine) Information:
a) CTL File
load data
infile 'c:\auto_rms.csv'
append into table autops_rms_ledger
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(arl_client_id,
ARL_CASH,
ARL_ADHOC)
b)autops_rms_ledger Table Structure
arl_client_id varchar2(30),
arl_cash number(12,3),
arl_ur number(12,3)
-
Attachment: auto_rms.csv
(Size: 0.25KB, Downloaded 2027 times)
|
|
|
|
| Re: EXTERNAL TABLE [message #609246 is a reply to message #609212] |
Tue, 04 March 2014 08:19 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I'm with Littlefoot. You "think" you are loading that file, but you are not. For external tables, the file must be on the server in the directory identified by the Oracle directory (not o/s directory) defined by wes_dir in your case.
That being said, is is a coincidence that there is a file called auto_rms.csv there already?
|
|
|
|
| Re: EXTERNAL TABLE [message #609265 is a reply to message #609246] |
Tue, 04 March 2014 23:21 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
WES_DIR is an oracle directory only, find below the create statement of the same and also we have granted read and write permission to user through which we execute the external query.
-- Create directory
create or replace directory WES_DIR
as '/cdsloutput/';
|
|
|
|
|
|
| Re: EXTERNAL TABLE [message #609272 is a reply to message #609269] |
Wed, 05 March 2014 00:42 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
Hi Littlefoot,
The said directory name WES_DIR (Created in Oracle ) which in turn points to /cdsloutput/ (linux server, where oracle was installed).
Even my below said query
select * from v$parameter
where name = 'utl_file_dir'
output : /cdsloutput/
All character data was getting loaded through external table, main concern is for number data i am getting the raised error.
|
|
|
|
|
|
| Re: EXTERNAL TABLE [message #609450 is a reply to message #609273] |
Fri, 07 March 2014 00:52 |
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
|
Member |
|
|
Hi Little Foot & Joy Division
After speculating this issue found something abnormal, i will be glad if you guys can suggest and help me to combat this issue.
Found that there was some special character which automatically appends to the last column in the csv file which i try to upload, hence the last column in the table was loaded with that value. There fore i could not able to do a number formatting. i have extracted and attached the output snapshot for your reference.
Thanks & Regards
Meena
[mod-edit: image inserted into message body by bb]
[Updated on: Sat, 08 March 2014 14:43] by Moderator Report message to a moderator |
|
|
|
| Re: EXTERNAL TABLE [message #609454 is a reply to message #609450] |
Fri, 07 March 2014 01:26 |
|
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Found that there was some special character which automatically appends to the last column in the csv file which i try to upload,
This is typically the case when you transfer a text file from Windows to *nix.
This transfer must be done in ASCII mode.
Now you can convert the file using dos2unix command.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
