Home » SQL & PL/SQL » SQL & PL/SQL » External table (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0)
External table [message #288858] Tue, 18 December 2007 23:47 Go to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Hii,

I am going to create an extarnal table in my database (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0).The script i wrote for this purpose is written below:

CREATE DIRECTORY ext_dir1 AS 'd:/ext';
grant read,write on directory ext_dir1 to user;

CREATE TABLE oldemp (
empno NUMBER, empname CHAR(20), addr char)
ORGANIZATION EXTERNAL
(--TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_dir1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad_emp'
LOGFILE 'log_emp'
FIELDS TERMINATED BY ','
(empno CHAR,
empname CHAR,
addr CHAR ))
LOCATION ('ext_table.txt'))
--PARALLEL 5
REJECT LIMIT 200;

then when i am going to query :-

select * from oldemp;
this gives me error :--

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file log_emp.log
OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 19
ORA-06512: at line 1

Please help me and let me know how rectify that error.
I already created the directory 'ext'.and inside that
'bad_emp.log','log_emp.log'.




Re: External table [message #288874 is a reply to message #288858] Wed, 19 December 2007 00:16 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

next time format your code.

CREATE TABLE oldemp 
(
empno NUMBER, 
empname CHAR(20), 
addr char(100)
)
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY EXT_DIR1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE EXT_DIR1:'bad_emp.bad'
LOGFILE EXT_DIR1:'log_emp.log'
FIELDS TERMINATED BY ','
(empno CHAR,
empname CHAR,
addr CHAR ))
LOCATION ('ext_table.txt'))
REJECT LIMIT 200;


regards,
Re: External table [message #288898 is a reply to message #288858] Wed, 19 December 2007 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ 'bad_emp' is not the same as 'bad_emp.log'
2/ check permissions at OS level

Regards
Michel
Re: External table [message #288900 is a reply to message #288898] Wed, 19 December 2007 00:43 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
How to check the permission??
Re: External table [message #288901 is a reply to message #288874] Wed, 19 December 2007 00:44 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Still it is giving the same error. What should I do?
Re: External table [message #288909 is a reply to message #288900] Wed, 19 December 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

How to check the permission??

Ritgh-click on directory -> Properties -> Security -> SYSTEM or your instance/listener owner and see permissions.

Regards
Michel
Re: External table [message #288924 is a reply to message #288909] Wed, 19 December 2007 01:17 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
I checked it.Folder is read-only.but files inside that are not read-only.Plz give any suggetion what to do??
Re: External table [message #288932 is a reply to message #288924] Wed, 19 December 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the folder in read/write.

Regards
Michel
Re: External table [message #288944 is a reply to message #288932] Wed, 19 December 2007 02:00 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
It is not possible to make the folder read/write.I am changing the status but when i am going to check the status again,it is showing the status read-only for the folder.But files inside the folder are still not read-only.
Re: External table [message #288948 is a reply to message #288944] Wed, 19 December 2007 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to click on "Advanced" and uncheck the option "Inherits from parent the permission...".

Regards
Michel
Re: External table [message #288956 is a reply to message #288948] Wed, 19 December 2007 02:36 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
On right click on the folder there is no such tab 'advanced'
Only three tab is there general,sharing,customized.And no such option specified by you.What to do??
Re: External table [message #288960 is a reply to message #288956] Wed, 19 December 2007 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you have a security tab?
It is in this tab you have the advanced button.

Regards
Michel
Re: External table [message #288985 is a reply to message #288960] Wed, 19 December 2007 03:44 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
No such exist sir.What to do??
Re: External table [message #288991 is a reply to message #288985] Wed, 19 December 2007 03:49 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you have a restricted access to the server.
If so ask your system administrator.

Regards
Michel
Previous Topic: diffence between procedure and function.
Next Topic: User and schema
Goto Forum:
  


Current Time: Wed Dec 07 14:08:07 CST 2016

Total time taken to generate the page: 0.11744 seconds