OraFAQ Forum: Server Utilities » External Table cannot be viewed....

Home » RDBMS Server » Server Utilities » External Table cannot be viewed.... (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

External Table cannot be viewed.... [message #548253]

Wed, 21 March 2012 01:46

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.....

Report message to a moderator

Re: External Table cannot be viewed.... [message #548264 is a reply to message #548253]

Wed, 21 March 2012 01:58

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Littlefoot
Messages: 21823
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)?

Report message to a moderator

Re: External Table cannot be viewed.... [message #548281 is a reply to message #548269]

Wed, 21 March 2012 02:45

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: External Table cannot be viewed.... [message #548290 is a reply to message #548281]

Wed, 21 March 2012 04:44

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..

Report message to a moderator

Re: External Table cannot be viewed.... [message #548294 is a reply to message #548290]

Wed, 21 March 2012 04:50

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ The directory and file must be on the server.
2/ You must answer our questions

Regards
Michel

Report message to a moderator

Re: External Table cannot be viewed.... [message #548295 is a reply to message #548290]

Wed, 21 March 2012 04:51

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

A good start would be to start answering questions people asked.

Report message to a moderator

Re: External Table cannot be viewed.... [message #548302 is a reply to message #548264]

Wed, 21 March 2012 05:13

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"

Report message to a moderator

Re: External Table cannot be viewed.... [message #548304 is a reply to message #548295]

Wed, 21 March 2012 05:14

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.

Report message to a moderator

Re: External Table cannot be viewed.... [message #548306 is a reply to message #548302]

Wed, 21 March 2012 05:19

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: External Table cannot be viewed.... [message #548308 is a reply to message #548269]

Wed, 21 March 2012 05:26

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.

Report message to a moderator

Re: External Table cannot be viewed.... [message #548309 is a reply to message #548308]

Wed, 21 March 2012 05:30

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

Barbara Boehmer
Messages: 9104
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;

Report message to a moderator

Re: External Table cannot be viewed.... [message #548401 is a reply to message #548379]

Wed, 21 March 2012 22:57

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: External Table cannot be viewed.... [message #548443 is a reply to message #548404]

Thu, 22 March 2012 05:03

cookiemonster
Messages: 13963
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.

Report message to a moderator

Previous Topic:	SQL Loader Help
Next Topic:	Unable to use REMAP_TABLE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 01:14:46 CST 2025