Read from a text file... 3

[CIMTEET]

I am trying to read text from a text file and need to use a different directory. In my reading I found how to do it using the init.ora file in the UNIX OS. Unfortunately my DBA is being rebellious and not allowing me to use the init.ora method because of having to bounce the dbase AND the fact that this would have to (in her eyes) be implemented world wide at all similar sites (that are closed to outside world). So my next thought would be how do I change this thing dynamically? In windows I found an article that describes this process but have not been able to find one for unix.

In the init.ora file there needs to be defined a file handle.

UTL_FILE_DIR = /greg/tmp

so in my program I can ...

DECLARE
config_file UTL_FILE.FILE_TYPE
BEGIN
config_file :=UTL_FILE.FOPEN ('/greg/tmp,'blah.txt', 'R')
......read operation......
END;

Is there any way at all in UNIX where you don't need to bounce the dbase by altering hte init.ora file??

 

[SantaMufasa]

So, to confirm, on your Compaq Alpha machine, you have both the Oracle database server and in that machine's C:\ root directory, you have a file named, "cimteet.txt"?

Please confirm.

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

 

[carp]

Also, make sure the user you are trying to run the query as has the proper permissions on the directory:

GRANT READ, WRITE ON DIRECTORY ALMInput TO <schema name>;

The directory belongs to SYS and, like anything else, other schemas must have the proper privileges to use the object. Of course, if you are logged in as SYS, then you're back to the file being the problem. If the file exists, then you may need to set the read/write privileges on the OS file AND directory.

 

[taupirho]

No the compaq alpha is a completely separate beast from my PC - different OS/directory structure etc ...

The C:\ root directory is on my PC. The db is on the alpha.
I can and mostly do connect to the db directly on the alpha. Ocassionally I connect to the db via the PC using regular client tools installed on the PC such as sqlplus, sqllddr tools etc .. (via sqlnet)

 

[SantaMufasa]

This feature ("external tables" on top of flat files) is a server-side feature, (as compared to client-side features like SQL*Plus interactions). The flat file(s) must reside in a directory/on a file system that the database server can "see".

Therefore, if you can somehow cause the Alpha to "see" your PC file system ("Samba-style", for example), you can make this happen on the alpha with the flat file(s) on your PC. Otherwise, you must get the files accessible to the Alpha.

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

 

[CIMTEET]

Carp
Permission with what reference? Oracle or the OS?

 

[CIMTEET]

Ok, so I have been referencing my text file reliably based on the answer up top. It keeps generating these log files though. How do I turn that off?

And I have created cursors that bring in the data frome the database. The number of messages change and the number of fields change. There are 2 cursors. They are executed by: Abbreviated

For rec_msgs IN crs_get_msg(msg_name1,msg_name2..16)LOOP
umi := rec_msgs.unique_msg_id;
FOR rec_fields IN crs_get_field(umi,field_name1..16)LOOP
END LOOP;
END LOOP;

I am getting no return when I think I should. The changing number of fields returned from the flat_file basically mean that only part of the msg_name1 and field_name vars are populated with useful information on the search but the rest of them are populated with a default value that won't be found in teh database. I have a better way to do this with text manip but there is not the time right now. My first question here is how many inputs can one put into a dynamic cursor, second, I tested putting junk and good into a cursor for processing and it returned what it found so I didn't think it would be an issue. I apologize for not putting the code in here. I have to type it in manually, no netword link for security reasons.

Greg

 

[CIMTEET]

I amazed myself again...there were spaces in my search strings, thats why there were no returns...yup...thats what it were. Makes sense. Anyhow, if someone could tell me how to turn off the log files that come when creating a table I would appreciate it.

 

[SantaMufasa]

Sorry, Cimteet, I didn't see your request until just now.

To get rid of all of the default files that come with an "external table", you can say:

...
organization external
(  type oracle_loader
   default directory ALMInput
   access parameters
   (records delimited by newline
    [b]NOBADFILE NODISCARDFILE NOLOGFILE[/b]
    fields terminated by ...   )

Let us know how this works for you.


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

 

[CIMTEET]

I found out that my DBA was trying to show me external tables but did not know how to do this herself. She liked the idea. Thank you again for your help. It seems like most of the stuff I have needed recently either I didn't know what to look for OR normal documentation did not have it. Even google was difficult to search through. What are some good references that are detailed, yet fairly simple to sift through??

Thanks again Dave
Greg

 

[CIMTEET]

Does Dasages provide remote training? Or possibley some online courses in oracle? I don't think I could convince my boss at this point for a plain ticket but I could possibly convince him in some correspondance learning.

 

[SantaMufasa]

Absolutely. In fact, everything we do, we do remotely, and tailored to the users' availability, to keep costs to a minimum.

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

 

[CIMTEET]

I emailed your Dasages account.