Home » RDBMS Server » Server Utilities » EXTERNAL TABLE
EXTERNAL TABLE [message #609124] Mon, 03 March 2014 02:25 Go to next message
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 35 times)
Re: EXTERNAL TABLE [message #609125 is a reply to message #609124] Mon, 03 March 2014 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you also post the target table CREATE TABLE statement?
Re: EXTERNAL TABLE [message #609126 is a reply to message #609125] Mon, 03 March 2014 02:27 Go to previous messageGo to next message
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)
)
Re: EXTERNAL TABLE [message #609127 is a reply to message #609126] Mon, 03 March 2014 02:30 Go to previous messageGo to next message
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 #609128 is a reply to message #609124] Mon, 03 March 2014 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
arl_cash integer,
arl_adhoc integer


"integer" must be CHAR.

Re: EXTERNAL TABLE [message #609131 is a reply to message #609128] Mon, 03 March 2014 02:42 Go to previous messageGo to next message
Littlefoot
Messages: 19653
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #609147 is a reply to message #609136] Mon, 03 March 2014 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So remove REJECT LIMIT (or set it to a higher level); as you can see, both Michel and me didn't face any issues with a CSV file you provided.
Re: EXTERNAL TABLE [message #609149 is a reply to message #609147] Mon, 03 March 2014 04:29 Go to previous messageGo to next message
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
Member
then also I am getting the same error while querying the external tab;e
Re: EXTERNAL TABLE [message #609151 is a reply to message #609149] Mon, 03 March 2014 04:30 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, post your current "CREATE TABLE autops_rms_ledger_ext" statement and attach CSV file you are trying to read.
Re: EXTERNAL TABLE [message #609153 is a reply to message #609151] Mon, 03 March 2014 04:36 Go to previous messageGo to next message
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 #609154 is a reply to message #609153] Mon, 03 March 2014 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I didn't mean that; review this message and post exactly the same as I did (which means that you should copy/paste your SQL*Plus session).

By the way, which database version do you use? Post result of
select * from v$version;
Re: EXTERNAL TABLE [message #609155 is a reply to message #609149] Mon, 03 March 2014 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the result of:
select value from V$NLS_PARAMETERS where PARAMETER='NLS_NUMERIC_CHARACTERS';

Use SQL*Plus and copy and paste your session as we did.

Re: EXTERNAL TABLE [message #609158 is a reply to message #609155] Mon, 03 March 2014 04:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4520
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 19653
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 Go to previous messageGo to next message
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 18 times)
Re: EXTERNAL TABLE [message #609246 is a reply to message #609212] Tue, 04 March 2014 08:19 Go to previous messageGo to next message
joy_division
Messages: 4520
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 Go to previous messageGo to next message
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 #609269 is a reply to message #609265] Wed, 05 March 2014 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure what your latest reply means. Did you understand what Joy Division said? As long as you probably did it right on Oracle side, this directory - /cdsloutput/ - must be created on a disk on your database server (not your own PC, unless your PC actually is the database server). It means that you have to connect to server console and issue "create directory" command (for example, on MS Windows that would be through Windows Explorer GUI or MKDIR command prompt command).

Did you do that?
Re: EXTERNAL TABLE [message #609272 is a reply to message #609269] Wed, 05 March 2014 00:42 Go to previous messageGo to next message
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 #609273 is a reply to message #609272] Wed, 05 March 2014 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then.

You never replied to this message. Could you do that now, please?
Re: EXTERNAL TABLE [message #609450 is a reply to message #609273] Fri, 07 March 2014 00:52 Go to previous messageGo to next message
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
./fa/11748/0/


[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 Go to previous message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
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.

Previous Topic: Please explain SQL* Loader with simple example
Next Topic: impdp
Goto Forum:
  


Current Time: Thu Oct 02 09:07:15 CDT 2014

Total time taken to generate the page: 0.09373 seconds