Home » RDBMS Server » Server Utilities » I have a one question regarding external table use in oracle? (windows 7)
I have a one question regarding external table use in oracle? [message #502562] Thu, 07 April 2011 05:47 Go to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
Hi,
I am trying to query an external table that points it file on my Windows 7 system. The Oracle server is running on a same machine

i have created a one folder in my "F" drive name of practice and there i have put my emp.csv file it contaion data
my emp.csv file is look like this

7782 CLARK MANAGER
7839 KING PRESIDENT
7934 MILLER CLERK
7566 JONES MANAGER
7499 ALLEN SALESMAN
7654 MARTIN ALESMAN
7658 CHAN ANALYST
7654 MARTIN ALESMAN
===========================
thann i have run this following steps on my sqlplus

conn sys as sysdba

than
SQL> create or replace directory emp_data_dir
SQL> as 'F:\practice';

SQL> grant read,write on directory emp_data_dir to ron;

SQL> conn ron/ron

create table emp1
(eno number(10),
ename varchar2(15),
dec varchar2(15))
organization external
(default directory emp_data_dir
access parameters
(records delimited by newline
fields terminated by ','
)
location ('f:\practice\emp.csv')
);

When I query the external table it returns the following error.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SPECIES_RATINGS TABLE
EMP TABLE
EMPLOYEE TABLE
EMP1 TABLE

SQL> select * from emp1;
select * from emp1
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04076: file name cannot contain a path specification: f:\practice\emp.csv
ORA-06512: at "SYS.ORACLE_LOADER", line 19
so any one give me a complite solution or any one give me a complite example on windows 7 how can i use external table in oracle 10g
Re: I have a one question regarding external table use in oracle? [message #502564 is a reply to message #502562] Thu, 07 April 2011 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOCATION in CREATE TABLE should contain file name only (i.e. no path):
LOCATION ('EMP.CSV')
Re: I have a one question regarding external table use in oracle? [message #502567 is a reply to message #502562] Thu, 07 April 2011 06:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you using CSV (comma separated values) or space separated values? Your data file doesn't match the access parameters.
Re: I have a one question regarding external table use in oracle? [message #502570 is a reply to message #502564] Thu, 07 April 2011 06:32 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
yeah now its working good

create table emp1
(eno number(10),
ename varchar2(15),
dec varchar2(15))
organization external
(default directory emp_data_dir
access parameters
(records delimited by newline
fields terminated by ','
)
location ('emp.csv')
);
==================

Re: I have a one question regarding external table use in oracle? [message #502573 is a reply to message #502564] Thu, 07 April 2011 06:44 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
i just want to ask u

is it possible can i insert raw in this external table

means if i insert raw on this emp.csv file using sqlplus so is it possible this raw inserted in my this emp.csv file..
Re: I have a one question regarding external table use in oracle? [message #502580 is a reply to message #502573] Thu, 07 April 2011 07:08 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you can't, external table are read-only one.

Regards
Michel
Previous Topic: error in load data using sqlloader
Next Topic: Loader loading some garbage characters at the end of the data field
Goto Forum:
  


Current Time: Fri Mar 29 06:16:45 CDT 2024