| Error with directory and Syntax for ceating directory (merged cross-posts) [message #128497] |
Tue, 19 July 2005 00:05  |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
I created directory by create directory command...After executing the command it shows directory created.But,I cant find the directory in that location
SQL> Create directory sharan_db as ‘E\sharan_db\’
SQL> /
Directory Created
SQL> grant all on directory sharan_db to public;
Grant succeeded
Then, i created a directory manullay through windows and placed a text file and tried to create a table with data from text file.Table created and am able to view description.
But select * from table_name gives error as below:
SQL> create table db(empno number(4),ename varchar2(20),
Deptno number(4))
Organization external
(type oracle_loader default directory sharan_db access parameters
(fields terminated by ‘,’) location(‘db.txt’));
SQL> Table created
SQL> desc db
Name Null? Type
----------------------------
Empno number(4)
Ename varchar2(20)
Deptno number(4)
SQL> select * from db;
Select * from db
*
ERROR at line 1:
ORA-29913 : Error in executing ODCIEXTTABLEOPEN call out
ORA-29400 : data catridge error
KUP-04063 : unable to open log file DB_744_360.log
OS error : The System cannot find the file specific
ORA-006512 : at “SYS.ORACLE_LOADER”,line 14
ORA-06512 : at line 1
Why am not able to view created directory?
Even i create directory manual and place a text file and when i try to create a table,why it shows error to view table data once it was created.
Note : Text file have data like 1001,'sharan',10
Pls clarify me.....
|
|
|
|
|
|
|
|
| Re: Error with directory [message #128552 is a reply to message #128497] |
Tue, 19 July 2005 06:21  |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
1>My question is why i am not able to see the directory (created through create directory command)in the path
2>Other question i have is, once i created directory and place a text file in that am able to create table with the data present in the text file( present like 1001,'sharan',10) and i can see description.Then why it shows data catridge error i try to view table data...
|
|
|
|
| Re: Error with directory [message #128562 is a reply to message #128552] |
Tue, 19 July 2005 07:29 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
The most probable reason is you have specified invalid OS
path in your CREATE DIRECTORY command:
"Create directory sharan_db as ‘E\sharan_db\’"
Try re-create your directory with correct OS path -
I think it has to be 'E:\sharan_db' on Windows.
Rgds.
|
|
|
|
| Re: Error with directory [message #128636 is a reply to message #128497] |
Tue, 19 July 2005 23:16 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
I created with the command Create directory sharan_db as ‘E:\’;
It showed directory created...When i look for the folder in E:\ i cant see it.
Even start-> search(in windows) gives no files found..
But,if i give again Create directory sharan_db as ‘E:\’;
it gives error directory already exists...
i created a directory sharan manullay through windows and placed a text file and tried to create a table with data from text file.Table created and am able to view description.
But select * from table_name gives error as below:
SQL> create table db(empno number(4),ename varchar2(20),
Deptno number(4))
Organization external
(type oracle_loader default directory sharan access parameters
(fields terminated by ‘,’) location(‘db.txt’));
SQL> Table created
SQL> desc db
Name Null? Type
----------------------------
Empno number(4)
Ename varchar2(20)
Deptno number(4)
SQL> select * from db;
Select * from db
*
ERROR at line 1:
ORA-29913 : Error in executing ODCIEXTTABLEOPEN call out
ORA-29400 : data catridge error
KUP-04063 : unable to open log file DB_744_360.log
OS error : The System cannot find the file specific
ORA-006512 : at “SYS.ORACLE_LOADER”,line 14
ORA-06512 : at line 1
what would be the actual problem? Once directory created why that folder not found...
Even then after i created another folder by name sharan and put a text file,db.txt why it gives error.I can see that file..table created am able to see description then why it gives error on viewing data..
Note: text file have data like 1000,'sharan'10
|
|
|
|
| Re: Error with directory [message #128659 is a reply to message #128497] |
Wed, 20 July 2005 01:09 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
In oracle, the create directory command does not create a directory on your filesystem (or a folder on your windows-disk), it merely creates a pointer within Oracle to an existing (or not) location in your filesystem.
You have to make sure the location (folder) existst.
SCOTT@ora9i>create directory my_dir as 'C:\Temp'
2 /
Directory created.
SCOTT@ora9i>create table my_tab
2 ( empno number(4)
3 , ename varchar2(20)
4 , Deptno number(4)
5 )
6 Organization external
7 ( type oracle_loader
8 default directory my_dir
9 access parameters
10 ( fields terminated by ','
11 )
12 location('db.txt')
13 )
14 /
Table created.
SCOTT@ora9i>select * from my_tab
2 /
EMPNO ENAME DEPTNO
---------- -------------------- ----------
12 John 10
14 Mary 20
And my c:\temp contains:
C:\Temp>dir db.*
Volume in drive C is LOCAL DISK
Volume Serial Number is XXXX-XXXX
Directory of C:\Temp
20-07-2005 08:02 22 db.txt
1 File(s) 22 bytes
btw, next time please copy and paste from sqlplus when you want to show something.
| Quote: |
(fields terminated by ‘,’) location(‘db.txt’));
SQL> Table created
|
this is definitely not a copy and paste.
hth
|
|
|
|
| Re: Error with directory [message #128874 is a reply to message #128497] |
Thu, 21 July 2005 00:50 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
Error Log file created as follows:
Log file opened at 7/19/05 21:59:36
Fiedl Definitions for table DB.s1
Record format UNLIMITD BY NEWLINE
Data in file has same endianness as the platform fields in Data Source
EMPNO char(255)
Terminated by ','
Trim whitespaces same as SQL Loader
ENAME char(255)
Terminated by ','
Trim whitespaces same as SQL Loader
DEPTNO char(255)
Terminated by ','
Trim whitespaces same as SQL Loader
KUP-040716:filename cannot contain in a path specification:E:\sharan_db:db.txt
Actually,text file has data like 1001,'sharan',10
This text file should be kept in E:\ right?
Pls help...
|
|
|
|
| Re: Error with directory [message #129055 is a reply to message #128874] |
Thu, 21 July 2005 14:22 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
A big part of the problem is that you are not posting exactly what you are trying and you are not trying exactly what is being suggested. For example, the only way that you get an error message like:
KUP-04063 : unable to open log file DB_744_360.log
is if you are either specifying db_744_360.log as a log file, which you have not posted or if you are creating a table named db_744_360, which you have not posted, and it is attempting to create a default log file. Another example, the only way that you get an error message like:
KUP-040716:filename cannot contain in a path specification:E:\sharan_db:db.txt
is if you incorrectly use something like:
location('e:\sharan_db:db.txt')
which you have not posted.
Please copy and paste all of the following into a .sql file and start that file from SQL*Plus. Do not just copy part of it and do not attempt to just retype it. Then, let us know if that works for you or, if not, what error message you get.
-- create a directory in your operating system:
HOST MD e:\sharan_db
-- create a text file with your data in that directory:
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 VERIFY OFF
SPOOL e:\sharan_db\db.txt
PROMPT 1001,'sharan',10
SPOOL OFF
START saved_settings
-- create an Oracle directory object that points
-- to the directory in your operating system:
CREATE OR REPLACE DIRECTORY sharan_db AS 'e:\sharan_db'
/
-- drop any previously existing table:
drop table db
/
-- create external table:
create table db
(empno number(4),
ename varchar2(20),
Deptno number(4))
Organization external
(type oracle_loader
default directory sharan_db
access parameters
(fields terminated by ',')
location('db.txt'))
/
-- confirm table structure:
DESC db
-- confirm data loaded:
SELECT * FROM db
/
|
|
|
|
| Syntax for creating directory [message #129402 is a reply to message #128497] |
Mon, 25 July 2005 02:03 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
In windows how to create directory.
I created directory as
create directory my_dir as 'E\sharan_db';
But in some places i saw it with back slash '/' like
SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';
can i create direcoty as
create directory my_dir as '/E/sharan_db';
|
|
|
|
|
|
| Re: Syntax for creating directory [message #129532 is a reply to message #129402] |
Mon, 25 July 2005 11:28 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Are you reading the answers that you have already been given and trying what is suggested, before posting the same question on another forum? As previously stated by multiple people, the directory must already exist in the operating system, on the server. The Oracle syntax just creates an Oracle directory object that points to a directory in the operating system, on the server. Oracle will accept any string and does not validate it until it tries to use that directory. As previously stated, on Windows, the proper syntax for creating that directory object, using a colon ":" is:
CREATE OR REPLACE DIRECTORY out_dir AS 'e:\sharan_db';
NOT:
create directory my_dir as 'E\sharan_db';
The script that I provided included complete code to both create the directory on the operating system and create the Oracle directory object that points to it. Did you try it? The code does assume that you have an E: drive on your server. On my computer, the E: drive is a CD-RW/DVD ROM drive. Perhaps that is your problem. Is there an E: drive on your server? Have you tried using C or D instead?
If something suggested does not work for you, it is proper to post what you tried and indicate what results you got on the same thread, rather than ignore the responses and re-post the same question on another forum.
I hope you now understand that the response to your question:
"can i create direcoty as create directory my_dir as '/E/sharan_db';"
is NO, you cannot create a directory or directory object that way.
|
|
|
|
| Re: Error with directory and Syntax for ceating directory (merged cross-posts) [message #129631 is a reply to message #128497] |
Tue, 26 July 2005 01:21 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
I created directory as
create directory my_dir as 'E:\sharan_db';
(while typeing in the forum i missed ':'..but i gave it correctly in creating it..
I tried only with E: becoz my all files and folders are there.So,i use always E;
Once directory created and also table created..why not displaying error and showing cannot read file..
I tried many and many times..all are correct,,but why am not getting it?
(When i searched for utl_file i saw as
SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';
thats i asked)
|
|
|
|
|
|
| Re: Error with directory and Syntax for ceating directory (merged cross-posts) [message #130254 is a reply to message #128497] |
Fri, 29 July 2005 00:54 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
I did the same as u all of u gave,,
Syntax is rite...
Table created but data not selected and shows the same error..
I think there is some error..
I have a doubt...In my PC i have Oracle8i(version 9.0...) and also Oracle9i(Version9.1...)..Oracle8i is connected to Forams and reports..But both have same username and password..I say baboo/sharan@hari...If i create a table and insert values the same table i can access in Oracle 9i also..
Actually i was earlier trying directory and external tables with Oracle8i..But yesterday i tried Oracle9i(same baboo/sharan@hari)..But again it shows error after table creation
SQL> select * from db;
Select * from db
*
ERROR at line 1:
ORA-29913 : Error in executing ODCIEXTTABLEOPEN call out
ORA-29400 : data catridge error
KUP-04063 : unable to open log file DB_744_360.log
OS error : The System cannot find the file specific
ORA-006512 : at “SYS.ORACLE_LOADER”,line 14
ORA-06512 : at line 1
But am getting desc correct....(same problem have from the begining)
Pls experts help...
|
|
|
|
| Re: Error with directory and Syntax for ceating directory (merged cross-posts) [message #130279 is a reply to message #130254] |
Fri, 29 July 2005 02:47 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Forget 8i - external tables were introduced in 9i. In 8i the 'CREATE DIRECTORY' command and the 'ORGANIZATION EXTERNAL' clause will not even be recognised. Plus it's out of date and unsupported.
The error message doesn't tell us anything new. Please post the output from the following query:
SELECT d.directory_path ||
CASE WHEN d.directory_path LIKE '%/%' THEN '/' ELSE '\' END ||
l.location AS filepath
FROM user_external_tables t
, all_directories d
, user_external_locations l
WHERE t.table_name = 'DB'
AND d.directory_name = t.default_directory_name
AND l.table_name = t.table_name;
The oracle user (not you) must be able to read that file from the operating system prompt (in Windows open a cmd window and try a "dir" command).
|
|
|
|
| Re: Error with directory and Syntax for ceating directory (merged cross-posts) [message #130304 is a reply to message #128497] |
Fri, 29 July 2005 04:07 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
First i checked with 8i only it creates directory and even external table...It works pefect and creates table.Even am able to see description...The error came as i gave..
Even in 9i i did same,table created but not able to see data..again same error
Select * from db
*
ERROR at line 1:
ORA-29913 : Error in executing ODCIEXTTABLEOPEN call out
ORA-29400 : data catridge error
KUP-04063 : unable to open log file DB_744_360.log
OS error : The System cannot find the file specific
ORA-006512 : at “SYS.ORACLE_LOADER”,line 14
ORA-06512 : at line 1
I have oracle 8i and 9i in the same machine...Both have same username,passowrd and hoststring..baboo/sharan@hari..If i create a table and insert values in oracle 8i,the same table and values i can access in 9i also using same baboo/sharan@hari.Even am able to update which reflects in 8i also
Anyway i use ur qyery in my homePC and let u know...
|
|
|
|
| Re: Error with directory and Syntax for ceating directory (merged cross-posts) [message #130370 is a reply to message #130304] |
Fri, 29 July 2005 10:22 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
| Quote: |
I have a doubt...In my PC i have Oracle8i(version 9.0...) and also Oracle9i(Version9.1...)
|
Your 8i database is version 9.0? I don't think that can be right. Please post the output of the following query:
SELECT * FROM v$version
WHERE banner LIKE 'Oracle%';
Or, if your Oracle account does not have access to V$VERSION, please start SQL*Plus and copy & paste the banner text it displays. For example, I get this:
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jul 29 16:16:50 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
