Home » RDBMS Server » Server Utilities » External Table cannot be viewed.... (Oracle 10g)
External Table cannot be viewed.... [message #548253] Wed, 21 March 2012 01:46 Go to next message
Abhijitsaha.uma
Messages: 10
Registered: March 2012
Location: Kolkata
Junior Member
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 21 11:52:03 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn abcd@'(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.155)(PORT
= 1521)))(CONNECT_DATA =(SERVICE_NAME = umitl)))'
Enter password: ****
Connected.
SQL> create directory aaaa as 'C:\my_oracle_files\'
2 ;

Directory created.

SQL> conn scott@'(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.155)(POR
T = 1521)))(CONNECT_DATA =(SERVICE_NAME = umitl)))'
Enter password: *****
Connected.
SQL> grant read on directory aaaa to abcd;

Grant succeeded.

SQL> grant write on directory aaaa to abcd;

Grant succeeded.

SQL> conn abcd@'(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.155)(PORT
= 1521)))(CONNECT_DATA =(SERVICE_NAME = umitl)))'
Enter password: ****
Connected.
SQL> create table ext_emp
2 (
3 emp_id varchar(10),
4 Login varchar(10)
5 )
6 ORGANIZATION EXTERNAL
7 (
8 TYPE ORACLE_LOADER
9 DEFAULT DIRECTORY aaaa
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE
13 BADFILE aaaa:'externalfile_closebal.bad'
14 LOGFILE aaaa:'externalfile_closebal.log'
15 DISCARDFILE aaaa:'externalfile_closebal.dsc'
16 FIELDS TERMINATED BY ','
17 MISSING FIELD VALUES ARE NULL
18 (emp_id CHAR(32),
19 Login CHAR(32)) )
20 LOCATION (aaaa:'tab.TXT')
21 )
22 REJECT LIMIT UNLIMITED
23 NOPARALLEL
24 NOMONITORING;

Table created.

SQL> select * from ext_emp;
select * from ext_emp
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file externalfile_closebal.log
OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 19


SQL> column emp_id format A15;
SQL> column login format A15;
SQL> select * from ext_emp;
select * from ext_emp
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file externalfile_closebal.log
OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Please Kindly give the solution.....
Re: External Table cannot be viewed.... [message #548264 is a reply to message #548253] Wed, 21 March 2012 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

John Watson wrote on Sat, 10 March 2012 11:29
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
...


Does the directory exist and is accessible and writeable to Oracle account?

Please Kindly follow the guide...

Regards
Michel

[Updated on: Wed, 21 March 2012 02:00]

Report message to a moderator

Re: External Table cannot be viewed.... [message #548269 is a reply to message #548264] Wed, 21 March 2012 02:09 Go to previous messageGo to next message
Littlefoot
Messages: 21528
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Directory (as an Oracle object) should point to a directory (in file system) which is created on a database server.

You chose 'C:\my_oracle_files\' which is ... where? Your PC (it would be OK if the database is running on it, locally)? Server (if not, should be)?
Re: External Table cannot be viewed.... [message #548281 is a reply to message #548269] Wed, 21 March 2012 02:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
It looks like you copied and pasted the 'c:\my_oracle_files' from my example in your other thread:

http://www.orafaq.com/forum/m/548279/43710/#msg_548279

I used that because c:\my_oracle_files is an operating system directory on my database server where the data file is and that Oracle has read and write access to. You need to use the directory that your data file is in and it must be on your server, not your client, and Oracle must have read and write access to it. When you create such an Oracle directory object, the path is not validated until you try to use it to create an external table.



Re: External Table cannot be viewed.... [message #548290 is a reply to message #548281] Wed, 21 March 2012 04:44 Go to previous messageGo to next message
Abhijitsaha.uma
Messages: 10
Registered: March 2012
Location: Kolkata
Junior Member
Respected Sir,

I had create that directory and saved my .dat file in it.
What are the thing that i must do to solve the problem...

Thanks for the reply..
Re: External Table cannot be viewed.... [message #548294 is a reply to message #548290] Wed, 21 March 2012 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ The directory and file must be on the server.
2/ You must answer our questions

Regards
Michel
Re: External Table cannot be viewed.... [message #548295 is a reply to message #548290] Wed, 21 March 2012 04:51 Go to previous messageGo to next message
Littlefoot
Messages: 21528
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A good start would be to start answering questions people asked.
Re: External Table cannot be viewed.... [message #548302 is a reply to message #548264] Wed, 21 March 2012 05:13 Go to previous messageGo to next message
Abhijitsaha.uma
Messages: 10
Registered: March 2012
Location: Kolkata
Junior Member
Yes the directory exists and read and write permission is granted to the oracle user "abcd"

Re: External Table cannot be viewed.... [message #548304 is a reply to message #548295] Wed, 21 March 2012 05:14 Go to previous messageGo to next message
Abhijitsaha.uma
Messages: 10
Registered: March 2012
Location: Kolkata
Junior Member
I am sorry sir.
I am new over here and also to oracle environment.
Re: External Table cannot be viewed.... [message #548306 is a reply to message #548302] Wed, 21 March 2012 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Once again, is the directory on the database server?

2/ The accesses I mentioned are the accesses to the OS directory from the oracle OS account, not the accesses to the Oracle directory from the Oracle account.

Regards
Michel
Re: External Table cannot be viewed.... [message #548308 is a reply to message #548269] Wed, 21 March 2012 05:26 Go to previous messageGo to next message
Abhijitsaha.uma
Messages: 10
Registered: March 2012
Location: Kolkata
Junior Member
The .dat file is on my PC and the database is also running on the same machine.
Re: External Table cannot be viewed.... [message #548309 is a reply to message #548308] Wed, 21 March 2012 05:30 Go to previous messageGo to next message
Abhijitsaha.uma
Messages: 10
Registered: March 2012
Location: Kolkata
Junior Member
I am totally confused and tensed.
Please can you start from the beginning.

How to find out that is the file is on the server or not and how to check whether it is accessable to the os account or not--I don't know.

[Updated on: Wed, 21 March 2012 05:30]

Report message to a moderator

Re: External Table cannot be viewed.... [message #548379 is a reply to message #548309] Wed, 21 March 2012 12:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
You said that you saved your ".dat" file, but your code says that your data file is tab.txt. What is the actual name of your data file?

What operating system does your computer use?

Please post the results of the following commands run from SQL*Plus:

host dir c:\my_oracle_files
select * from all_directories;



Re: External Table cannot be viewed.... [message #548401 is a reply to message #548379] Wed, 21 March 2012 22:57 Go to previous messageGo to next message
Abhijitsaha.uma
Messages: 10
Registered: March 2012
Location: Kolkata
Junior Member
Dear Madam,

I am using Windows XP Operating Sustem.
And my requirement is to import the datas in a *.dat file but when i am unable to do the same, I tried with *.txt file.

After running this
"host dir c:\my_oracle_files
select * from all_directories;"

I got the following output.



SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 22 09:11:06 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> host dir c:\my_oracle_files

SQL> select * from all_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ADMIN_DIR
C:\ADE\aime_10.2_nt_push\oracle/md/admin

SYS DATA_PUMP_DIR
c:\oracle\product\10.2.0\admin\umitl\dpdump\

SYS EXT_DIR
/app/oracle/flatfile


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS WORK_DIR
C:\ADE\aime_10.2_nt_push\oracle/work

SYS ADMIN_LOG_DIR
/flatfiles/log

SYS ADMIN_BAD_DIR
/flatfiles/bad


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS EXT
c:\external

SYS EXTERNALLLY
c:\externallly

SYS EXTERNALLY
c:\my_oracle_files


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS EXT_TAB_DIR
c:\my_oracle_files

SYS EXTTABDEMO
C:\oracle\external_table_dest

SYS EXTTABDIR
C:\oracle\external_table_dest


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS AAAA
C:\my_oracle_files\


13 rows selected.

SQL>

[Updated on: Wed, 21 March 2012 22:59]

Report message to a moderator

Re: External Table cannot be viewed.... [message #548404 is a reply to message #548401] Thu, 22 March 2012 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Are you running this on the database server?
2/ Use DOS version of SQL*PLus, here we do not see the result of host command (don't you see you didn't post it)

So open a DOS box and execute (and post) the result of:
set
dir c:\my_oracle_files
sqlplus /nolog
connect ...
exit


Before, Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: External Table cannot be viewed.... [message #548443 is a reply to message #548404] Thu, 22 March 2012 05:03 Go to previous message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
Let's clear something up right now:
Is the database itself installed on your PC? Not sqlplus, the database.
If it's not on your PC is it on another PC, or is it on a unix/linux server?


Your directories are a mess. Some look like windows directories, some look like unix directories.
Some are an invalid mix, eg:
SYS ADMIN_DIR
C:\ADE\aime_10.2_nt_push\oracle/md/admin

That's got forward slashes and back slashes. Windows directories only have back slashes. Unix ones only have forward slashes.
So that directory doesn't exist.
Previous Topic: SQL Loader Help
Next Topic: Unable to use REMAP_TABLE
Goto Forum:
  


Current Time: Fri Jan 17 17:49:19 CST 2020