How can I read a file that is on a seperate machine? 1

[randall2nd]

The short of it.

Currently we are running an oracle 8i(8.1.7) database on one machine(222.222.222.001) oracle report server on a second macine(222.222.222.002). When the report server generates a report it gets saved to /tmp/reports on the second machine.

Which works great so that they can be dislayed on the web.

But now I need to use a stored procedure to be able to read in some of the reports on the first machine.

Is there a way to read in a file that exists on a seperate machine.

If I am not being clear please ask questions.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[SantaMufasa]

Randall,

Since the report file system you mentioned is "/tmp/reports", I infer that this is a Unix-based system. As such, if you Unix administrator "shares" the file system from the source machine and "mounts" the shared file system on the target machine, you can refer to files on the remote file system as though they resided on your local machine.

Talk to your Unix admin and s/he can make this happen for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:07 (01Jul04) UTC (aka "GMT" and "Zulu"), 13:07 (01Jul04) Mountain Time)

 

[randall2nd]

Thank you for posting, yes they are both on Unix platforms.

I talked to the Admin, but he started ranting about securtity issues and laughing. I did not get all of it, but something about the headquarters not allowing it.

What are the security issues for sharing a folder from one unix machine to the next.

We are on a very high security network.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[SantaMufasa]

Randall,

If you Unix admin is going to "Play God" instead of suggest methods for being helpful, then I can suggest an end-around run that does not infringe on o/s security, but gets you what you want, provided the following:

1) You have an Oracle user-name and password on the remote machine
2) The "utl_file_dir" parameter on the remote Oracle instance is either "*" or inclusive of the path where your flat file resides.
3) The remote username has "CREATE TABLE" privilege
4) The local Oracle user has "CREATE DATABASE LINK" privileges.

Give these capabilities, you then do the following:
1) Log into the remote-instance Oracle account
2) Using Oracle's UTL_FILE package that reads (and writes, and appends) flat files, you read the flat file into a table (example: "transfer_tab") that you create for relatively temporary purposes. The table should probably have a sequence number (example: "seq_id") for one column and "txt" for the second column.
3) Log into the local-instance Oracle account.
4) "CREATE DATABASE LINK yada..." to the remote-instance account.
5) "SELECT txt FROM transfer_tab@yada order by seq_id;"

This allows you logical access to the data on the remote flat file. If you have additional questions on how to effect any of these steps, please post your questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:29 (01Jul04) UTC (aka "GMT" and "Zulu"), 13:29 (01Jul04) Mountain Time)

 

[randall2nd]

Yea he can be a bit of a pain sometimes.

I love your work around.

I will try it out and let you know how it goes.

I am thinking about making the process on the remote machine a stored procedure that I would call from the Main Db machine, and just pass the name of the file to read in.

You see any problems/issues with doing it that way?

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[SantaMufasa]

Thanks, Randall. I see no problem with resolving your problem in the fashion you mentioned (I actually do it that way myself.)

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:22 (01Jul04) UTC (aka "GMT" and "Zulu"), 14:22 (01Jul04) Mountain Time)