Home » RDBMS Server » Server Utilities » Directory in Separate Server, External_Table ((Oracle 10g 10.2.0.1.0))
Directory in Separate Server, External_Table [message #416157] Thu, 30 July 2009 21:56 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi Experts,

I just still can't find any work around with this. Please bear with me.

In a procedure I used an External Table to read files from a Unix Directory and process and use it to update tables in the database (all of these in the same server).

Suddenly, I am facing two servers (Server A and Server B). Server A doesn't have any database (Oracle) installed, these is where the input files (to be processed) will be sent. Oracle is installed in Server B (cannot create physical directories here). How can I access the files sent to Server A, with my PL/SQL procedure is in Server B?

I am using an External table with my source directory is specified on the same server. But now my source directory is in another server.

Directory:

CREATE OR REPLACE DIRECTORY 
 AS MY_TABLE_SRC_DIR
'my/path/is/here/source';


GRANT READ, WRITE ON DIRECTORY  MY_TABLE_SRC_DIR TO SYSTEM WITH GRANT OPTION;


External Table:

CREATE TABLE MY_TABLE
(
  FILENAME             VARCHAR2(5 BYTE),
.
.
.
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY MY_TABLE_SRC_DIR
     ACCESS PARAMETERS 
       ( records delimited by newline
      NOLOGFILE
      fields terminated by '|' 
         missing field values are null
                  )
     LOCATION (MY_TABLE_SRC_DIR:'Input_File123.TXT')
  )
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;




Any ideas how can i do these, or access the files in another server using an external table?

Is there an option in CREATE DIRECTORY where i can specify the dir path that its located in another server? So far i can't find any...

Edit: Typo

Thanks and Regards,
Wilbert

[Updated on: Thu, 30 July 2009 22:01]

Report message to a moderator

Re: Directory in Separate Server, External_Table [message #416158 is a reply to message #416157] Thu, 30 July 2009 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
NFS mount the directory from ServerB which contains the data files onto ServerA.

BTW, it is not a good idea to relative path names "my/path/is/here/source" but instead use absolute path names "/vol1/my/path/is/here/source"
Re: Directory in Separate Server, External_Table [message #416161 is a reply to message #416158] Thu, 30 July 2009 22:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Alternatively, use SQL*Loader to load a heap organised table over the network and then perform your SQL.

Ross Leishman
Re: Directory in Separate Server, External_Table [message #416188 is a reply to message #416157] Fri, 31 July 2009 01:15 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Thanks for the replies BlackSwan and rleishman.

* What will I expect using NFS? From what I have read regarding NFS, I will ask the admin of Server B (where the files will be sent) to map the path of the source directory.

Sample Mapped NFS Path: /vol1/other/server/path/source1


Then with NFS, Oracle on Server A will treat the path as a local directory. If this assumption is correct I will just declare the mapped NFS path to oracle, like this?

CREATE OR REPLACE DIRECTORY 
 AS MY_TABLE_SRC_DIR
/vol1/other/server/path/source1';


GRANT READ, WRITE ON DIRECTORY  MY_TABLE_SRC_DIR TO SYSTEM WITH GRANT OPTION;


* Regarding using SQL *Loader, can this be done even if the directory I'd like to access is in another server?

* Can I execute these command in PL/SQL alone, include this code in my procedure?

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log


Again, many thanks for the valuable suggestions. Much appreciated.


Regards,
Wilbert
Re: Directory in Separate Server, External_Table [message #416322 is a reply to message #416188] Fri, 31 July 2009 13:06 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
wmgonzalbo wrote on Fri, 31 July 2009 02:15


* What will I expect using NFS? From what I have read regarding NFS, I will ask the admin of Server B (where the files will be sent) to map the path of the source directory.



No, you have to mount server A's filesystem onto Server B. (you have to mount onto the server with the database).

[added] Upon reading further, I guess I misread, but yes, you can mount the DB filesystem onto the server where the files are going, but generally it's done the way I said initially; you would mount a filesystem ONTO the database server, not the other way around.

[Updated on: Fri, 31 July 2009 13:09]

Report message to a moderator

Previous Topic: EXPDP for XMLSchema objects
Next Topic: SQLLDR Carriage Return
Goto Forum:
  


Current Time: Fri Dec 09 11:59:34 CST 2016

Total time taken to generate the page: 0.08429 seconds