Home » RDBMS Server » Server Administration » External Table is loading data (Oracle 11g, Developer 10gR2, Windows XP)
External Table is loading data [message #409764] Tue, 23 June 2009 23:17 Go to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member


I have created an external table but select statement is not working can any body please help! I am attaching my code and text data with one location.jpg file.

My database in developer suite are in the same machine.

I have searched the forum for such posting but the solutions are not to my requirement.





SQL> CREATE OR REPLACE DIRECTORY fingerprint_dir AS 'g:\oradir\fingerprint';

Directory created.

SQL> GRANT READ  ON directory fingerprint_dir to public;

Grant succeeded.

SQL> GRANT WRITE ON directory fingerprint_dir to public;

Grant succeeded.

SQL> 
SQL> DROP   TABLE data.ext_fingerprint PURGE;

Table dropped.

SQL> CREATE TABLE data.ext_fingerprint 
  2    (
  3      userid  VARCHAR2(10) , 
  4      name    VARCHAR2(50), 
  5      dt      VARCHAR2(20) , 
  6      wkday   VARCHAR2(10) , 
  7      firstin VARCHAR2(10) , 
  8      lastout VARCHAR2(10)  
  9    ) 
 10   ORGANIZATION EXTERNAL  
 11   (
 12    TYPE              oracle_loader 
 13    DEFAULT DIRECTORY fingerprint_dir 
 14    ACCESS PARAMETERS 
 15       (
 16        RECORDS DELIMITED BY newline 
 17        SKIP 1 
 18        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 19         (
 20           userid  CHAR(10) , 
 21           name    CHAR(50), 
 22           dt      CHAR(20) , 
 23           wkday   CHAR(10) , 
 24           firstin CHAR(10) , 
 25           lastout CHAR(10)  
 26         )
 27       )
 28    LOCATION ('report.csv') 
 29   )
 30   REJECT LIMIT 1
 31  ;

Table created.

SQL> 
SQL> select * from data.ext_fingerprint order by 1,3;
select * from data.ext_fingerprint order by 1,3
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file g:\oradir\fingerprint\EXT_FINGERPRINT_2040_17328.log



Regards-

G. Hossain
  • Attachment: report.csv
    (Size: 0.18KB, Downloaded 136 times)
Re: External Table is loading data [message #409766 is a reply to message #409764] Tue, 23 June 2009 23:20 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

I also attach the location map file location.jpg for kind information, please.

Suggestion at http://www.orafaq.com/forum/t/49367/2/ did not come to any help to me.

Can anybody please help.
  • Attachment: location.JPG
    (Size: 19.88KB, Downloaded 129 times)

[Updated on: Tue, 23 June 2009 23:24]

Report message to a moderator

Re: External Table is loading data [message #409769 is a reply to message #409764] Tue, 23 June 2009 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64125
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle is not able to access (write) log file in a remote directory.

Regards
Michel
Re: External Table is loading data [message #409776 is a reply to message #409769] Tue, 23 June 2009 23:50 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

I have disabled the Antivirus McAfee and
Granted Full Permission to everyone to the directory.

How can I make oracle able to write to the directory? I mean what should I do now to make oracle able to write to the directory. The directory is on local hard disk. I also tried in C: drive. But for no use.

Best Regards.

G. Hossain

[Updated on: Tue, 23 June 2009 23:55]

Report message to a moderator

Re: External Table is loading data [message #409778 is a reply to message #409764] Tue, 23 June 2009 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>How can I make oracle able to write to the directory?
For some version of Oracle "directory" did/does NOT include volume [g:]
Re: External Table is loading data [message #409779 is a reply to message #409778] Tue, 23 June 2009 23:58 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member


I also tried on drive C: but for no use. Can any body please help.

Best regards.

[Updated on: Wed, 24 June 2009 00:11]

Report message to a moderator

Re: External Table is loading data [message #409867 is a reply to message #409779] Wed, 24 June 2009 04:57 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Quote:

Oracle is not able to access (write) log file in a remote directory.



CAN ANYBODY PLEASE TELL WHY ORACLE IS NOT ABLE TO ACCESS THE LOG FILE AND HOW I CAN MAKE ORACLE WRITE THE LOG FILE AS DISCUSSED IN PRECEEDING MESSAGES?

I ALSO TRIED THIS FROM C:\oratext directory. But, the error message is the same.

Quote:

SQL> CREATE OR REPLACE DIRECTORY fingerprint_dir AS 'C:\oratext';

Directory created.

SQL> GRANT READ ON directory fingerprint_dir to public;

Grant succeeded.

SQL> GRANT WRITE ON directory fingerprint_dir to public;

Grant succeeded.

SQL>
SQL> DROP TABLE data.ext_fingerprint PURGE;

Table dropped.

SQL> CREATE TABLE data.ext_fingerprint
2 (
3 userid VARCHAR2(10) ,
4 name VARCHAR2(50) ,
5 dt VARCHAR2(20) ,
6 wkday VARCHAR2(10) ,
7 firstin VARCHAR2(10) ,
8 lastout VARCHAR2(10)
9 )
10 ORGANIZATION EXTERNAL
11 (
12 TYPE oracle_loader
13 DEFAULT DIRECTORY fingerprint_dir
14 ACCESS PARAMETERS
15 (
16 RECORDS DELIMITED BY newline
17 SKIP 1
18 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
19 (
20 userid CHAR(10) ,
21 name CHAR(50),
22 dt CHAR(20) ,
23 wkday CHAR(10) ,
24 firstin CHAR(10) ,
25 lastout CHAR(10)
26 )
27 )
28 LOCATION ('report.csv')
29 )
30 REJECT LIMIT 1
31 ;

Table created.

SQL>
SQL> select * from data.ext_fingerprint order by 1,3;
select * from data.ext_fingerprint order by 1,3
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file C:\oratext\EXT_FINGERPRINT_2040_18324.log





Best regards.

[Updated on: Wed, 24 June 2009 05:08]

Report message to a moderator

Re: External Table is loading data [message #409882 is a reply to message #409867] Wed, 24 June 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64125
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your Oracle instance owner is not allowed to write into this directory or file.

Regards
Michel
Re: External Table is loading data [message #410027 is a reply to message #409882] Wed, 24 June 2009 22:03 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Thank you for your kind attention to my problem!

Quote:

So your Oracle instance owner is not allowed to write into this directory or file.



Q1. Who might be the owner of my oracle instance, ora_dba?
Q2. How can I know the name of my oracle instance owner?

I am an administrator user of the Windows XP. I have granted full/all permissions of the directory to everyone, to user ora_dba and administrators. But the error message did not change.

Kind regards.

[Updated on: Wed, 24 June 2009 22:08]

Report message to a moderator

Re: External Table is loading data [message #410028 is a reply to message #409764] Wed, 24 June 2009 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I want to move my undo and temporary tablespace to new path because of space issue. I am using Oracle 10g release 2 and working on production server can't take shutdown without prior permission.


Provide proof via command line along with CUT & PASTE

[Updated on: Wed, 24 June 2009 22:11]

Report message to a moderator

Re: External Table is loading data [message #410030 is a reply to message #410028] Wed, 24 June 2009 22:26 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Dear Blackswan,

Quote:

>I want to move my undo and temporary tablespace to new path because of space issue. .........

Provide proof via command line along with CUT & PASTE



Because it is for space issue I would prefer adding extra datafiles in the new location to the exising table spaces, for example:

ALTER TABLESPACE tablespacename ADD datafile 'F:\DataFiles\DataFile08.dbf' size 100M REUSE AUTOEXTEND ON ; 


I wonder the reason of your such query reply in my other type of question posting.

Regards.

[Updated on: Wed, 24 June 2009 22:32]

Report message to a moderator

Re: External Table is loading data [message #410033 is a reply to message #409764] Wed, 24 June 2009 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I wonder the reason of your such query reply in my other type of question posting.
'cuz I'm not paying close enough attention.

Sorry about the confusion.
Re: External Table is loading data [message #410037 is a reply to message #410033] Wed, 24 June 2009 23:01 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

It's Ok. Nothing to be sorry, it's just a confusion.

Regards.
Re: External Table is loading data [message #410044 is a reply to message #409764] Wed, 24 June 2009 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Let me try again.

> I have granted full/all permissions of the directory to everyone, to user ora_dba and administrators.

Please post proof that statement above is correct.
Re: External Table is loading data [message #410045 is a reply to message #410027] Wed, 24 June 2009 23:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64125
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Q1. & Q2. The one that starts the service.

Regards
Michel


Re: External Table is loading data [message #410049 is a reply to message #410044] Wed, 24 June 2009 23:38 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member


A jpg file, showing directory permission is attached herewith for your kind information.

Regards.
Re: External Table is loading data [message #410051 is a reply to message #410049] Wed, 24 June 2009 23:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64125
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA_DBA is irrelevant for file access.
Who start the service?

Regards
Michel

[Updated on: Wed, 24 June 2009 23:44]

Report message to a moderator

Re: External Table is loading data [message #410055 is a reply to message #409764] Wed, 24 June 2009 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>A jpg file,
Those who live by the GUI, get to suffer from the GUI.

I suspect the problem is due to CASE sensitivity, you decline to provide evidence.

Can't help those whose refuse to help themselves.
Re: External Table is loading data [message #410058 is a reply to message #410055] Thu, 25 June 2009 00:02 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

As we know Oracle for windows-32 bit is not case sensitive, except for Password (please correct me if I am wrong). Still I tried by changing to all lower case.

SQL> conn data/ioiuuyjkjjkghhg@jstt
Connected.
SQL> CREATE OR REPLACE DIRECTORY fingerprint_dir AS 'c:\oratext'; 

Directory created.

SQL> GRANT READ  ON directory fingerprint_dir to public; 

Grant succeeded.

SQL> GRANT WRITE ON directory fingerprint_dir to public; 

Grant succeeded.

SQL> 
SQL> DROP   TABLE data.ext_fingerprint PURGE;

Table dropped.

SQL> CREATE TABLE data.ext_fingerprint 
  2    (
  3      userid  VARCHAR2(10) , 
  4      name    VARCHAR2(50) , 
  5      dt      VARCHAR2(20) , 
  6      wkday   VARCHAR2(10) , 
  7      firstin VARCHAR2(10) , 
  8      lastout VARCHAR2(10)  
  9    ) 
 10   ORGANIZATION EXTERNAL  
 11   (
 12    TYPE              oracle_loader 
 13    DEFAULT DIRECTORY fingerprint_dir 
 14    ACCESS PARAMETERS 
 15       (
 16        RECORDS DELIMITED BY newline 
 17        SKIP 1 
 18        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 19         (
 20           userid  CHAR(10) , 
 21           name    CHAR(50), 
 22           dt      CHAR(20) , 
 23           wkday   CHAR(10) , 
 24           firstin CHAR(10) , 
 25           lastout CHAR(10)  
 26         )
 27       )
 28    LOCATION ('report.csv') 
 29   )
 30   REJECT LIMIT 1
 31  ;

Table created.

SQL> 
SQL> select * from data.ext_fingerprint order by 1,3;
select * from data.ext_fingerprint order by 1,3
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file c:\oratext\EXT_FINGERPRINT_2040_17812.log



Kindly clarify me as to what type of more evidence I can provide.

Best regards.

G. Hossain.

[Updated on: Thu, 25 June 2009 00:06]

Report message to a moderator

Re: External Table is loading data [message #410060 is a reply to message #409764] Thu, 25 June 2009 00:10 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>As we know Oracle for windows-32 bit is not case sensitive
OK, we agree.
But does not Windoze treat everything as UPPERCASE?

Does Oracle's 'c:\oratext' match C:\ORATEXT?

Since I refuse to do Windoze, I can't test.
Re: External Table is loading data [message #410063 is a reply to message #410060] Thu, 25 June 2009 00:21 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member


I have changed physical directory name in windows explorer and directory assignment to UPPERCASE but the error remains the same.

CREATE OR REPLACE DIRECTORY fingerprint_dir AS 'C:\ORATEXT'; 


Best regards.
Re: External Table is loading data [message #410064 is a reply to message #410051] Thu, 25 June 2009 00:23 Go to previous message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

I, user GHossain, start the service.

Quote:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

D:\Documents and Settings\GHossain>net stop OracleServiceJSTT
The OracleServiceJSTT service is stopping......
The OracleServiceJSTT service was stopped successfully.


D:\Documents and Settings\GHossain>net start OracleServiceJSTT
The OracleServiceJSTT service is starting..........
The OracleServiceJSTT service was started successfully.


D:\Documents and Settings\GHossain>



User GHossain is an Administrator and have full permission on the directory C:\ORATEXT.

Best regards.

[Updated on: Thu, 25 June 2009 00:24]

Report message to a moderator

Previous Topic: Dedicated USR1234 to instance1 only
Next Topic: ora 01861
Goto Forum:
  


Current Time: Wed Dec 07 06:43:10 CST 2016

Total time taken to generate the page: 0.16107 seconds