Errors creating directory 1

[CIMTEET]

I created a program that creates a directory that references a text file as if it were a database. Well, it works fine. I moved it to another server created all the proper directories and text files and these errors popped.

ORA-29913 Error in executing ODCIEXTTABLEOPEN callout
ORA-29400 data cartridge
.
.
.
KUP-04040 file Cimteet.txt in GregScript not found

GregScript is the name of my directory, in it is the address I need. Cimteet.txt is the text file I am reading from .

For some reason it won't open the external table.

Any thoughts

Greg

 

[SantaMufasa]

Greg,

I've had similar errors occur while attempting to access a flat file as a table. In each of my cases, it has been a PICNIC error (Problem In Chair, Not In Computer).

What operating system are you using?

Does the directory you are trying to create reside on a file system that is accessible to the server upon which the database resides?

Does the spelling of the DIRECTORY's file-system-path definition match, character-by-character, the spelling of the actual path? (And same for the file name?)

I moved it to another server created all the proper directories and text files and these errors popped.

What is the it to which you refer, and what code did you use to do it? (Please post the code here.)


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

It also said at the end :

OS error No file found.

There is a file there and the directory exists and the syntax seems correct. Would read write priviliges cause this.

 

[SantaMufasa]

Let's have you post the answers to my questions, above, and then we can talk further.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

I knew you would ask me that. I would have to bring the code in and type it manually...grrrrr. Its unix.

create or replace directory GregScript as '/home/marting/test'
create table cimteet (txt varchar2(1000))
organization external
( type oracle_loader
default directory GregScript
access parameters
(records delimited by newline
fields terminated by '^'
)
location ('Cimteet.txt')
)
reject limit unlimited;

This same syntax was used on the other server and it worked great. Taking it upstairs on a network called mars, all the sudden these errors came up. I had to ftp them to mars. So my first working directory was connected but it took more than a file copy to get them there.

 

[SantaMufasa]

Okay, the next thing that I would try is to connect to a SQL*Plus session on the machine where you are having the problem, then at the SQL> prompt, issue this command:

SQL> host ls -l /home/marting/test/Cimteet.txt

...and let us know the response. (If it responds with: "/home/marting/test/Cimteet.txt: No such file or directory", that explains why Oracle is throwing errors.)

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

It said no such file or directory. But its there from the command prompt. Why doesn't oracle see it?

 

[SantaMufasa]

To help isolate your problem, please post the results to these Unix-command-line statements:

<o/s prompt> id
<o/s prompt> ls -l /home
<o/s prompt> ls -l /home/marting
<o/s prompt> ls -l /home/marting/test

Those results should enlighten us to a resolution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

id output:: uid=1593(marting) gid=10(staff)
ls -l /home :: Showed all directories including marting
ls -l /home/marting:: showed all files and directores including test
ls -l /home/marting/test:: showed all files including Cimteet.txt

 

[CIMTEET]

Another note.

When executing, I get the directory and table created outputs. Then I have a prompting I fulfill with an Id I have to enter. Then the error occurs. The first thing I do with Cimteet.txt is run a cursor against it to bring in the data.

I don't know if that means anything to you.

 

[SantaMufasa]

Actually, Greg, what I was looking for with the "ls -l" is to confirm:

a) permissions
b) owner/group
c) spellings of subject directories/files

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

Does owner/group have to do with permissions? I have repeatedly gone over the spellings of subject directories and files in the sql code. I ftp'd copies of the files over so they haven't changed. I don't know about the owner group thing. I have written code before to write to certain portions of hte dbase before under the username session1 in oracle. These were temp tables though. Its the same thing I am using now.

 

[SantaMufasa]

Greg, The permissions on a file (in Unix) indicate what a specific user (and her/his Unix Group) can do to that file. In your case [uid=1593(marting) gid=10(staff)], your userid is "marting" and your group for that user is "staff". If your "ls -l" for the file "Cimteet.txt" returned:

# ls -l Cimteet.txt
-rwxrwx---   1 oracle   dba        17 Mar 29 11:43 Cimteet.txt

...this means that "oracle" owns the file, that the user "oracle" is member of a group named "dba", and the "-rwxrwx---" means that the user "oracle" can read, write, and execute "yada.txt", that anyone that is a member of the "dba" group can also read, write, and execute "yada.txt", and that any other user (that is not "oracle" or a member of the "dba" group) cannot do anything to "yada.txt" (by virtue of the "---" privileges.)

That is why I was interested in the permissions.

But unless we can figure out why the command

SQL> host ls -l /home/marting/test/Cimteet.txt

...returns "...no such file or directory...", we won't be able to get your code to behave properly. Do you (or any of your colleagues) have any suggestions why, if the file appears to exist, you are getting the "...no such file or directory..." error?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

DBA just told me a nice way to find a different way to do this. She suggested sql loader. Basically we are under tight config management. She threw all these acronyms at me that would cause her extra work. Then she pulled out the mighty "I am the DBA" card, of which I am not able to trumph. This is a permissions thing. I am out for the week and weekend. Be back Monday.

 

[CIMTEET]

I backed her in a corner though where she had to pull out the 'Cards'. Meaning she really couldn't hate on the method but on the work it would cause.

 

[SantaMufasa]

Frankly, Greg, your method ("external tables", above) already uses the SQL*Loader executables and syntax, so, you will (most likely) run into similar errors that you are encountering now.

Note in your code above:

...type oracle_loader
...(records delimited by newline
    fields terminated by '^'...

...these clauses are all references to SQL*Loader interaction.

Also, on a political issue, help your DBA remember that s/he is there to "Serve and Protect", not to erect roadblocks. (I know about DBAs...I are one.<grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

I was just encouraged to take a different route by a brick wall. SQL Loader was said to have different routes that could get me around this.(the dba said this) Is it possible that I won't run into permission issues. Also utl_file can read stuff. The alternative is using Perl. A language I don't know, which I could learn but I am not sure how to transfer data form a Perl to pl/sql script. It almost seems that it would be an unneeded step at this point. Even though learning perl would be helpful.