Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


how i can read text file located on a different server then that of my database

Posted on 2008-11-05
Medium Priority
Last Modified: 2013-12-19
i need to access and read some text file located on a server different from that where my database is located. i need to read the data from these files to load them in table in my database. for some reason i cannot setup the procedure  that i have built to read the files from the other server. but when i use a test path located on the database machine, i can read a test file.
in the proceudre  i am using ths syntax

-- grant permission to frost_addons
dbms_java.grant_permission('FROST_ADDONS','SYS:java.net.SocketPermission','ip address:port no ','connect, listen,accept,resolve');

-- get file quantity

select recon_GetFileqty(v_path2) into v_file_qty from dual;
Question by:marcia1
  • 2
LVL 48

Expert Comment

ID: 22894629

For UTL_FILE's procedures to access server directories the directories to
be accessed must be specified in the init.ora file, for example :


Quotes and a trailing \ are not necessary.

1) Long filenames or directory names with mixed case

For this to work the directory specified for use in the UTL_FILE procedures
must exactly match (including case) that specified in UTL_FILE_DIR. Although
the UTL_FILE_DIR init.ora parameter does not have to exactly match the actual
directories case.

e.g. UTL_FILE_DIR=E:\LogDirectory  

2) Directories with space characters

For this to work the directory must be delimited by double quotes in the
init.ora parameter file.


3) Making multiple directories available

To specify multiple directories have a separate UTL_FILE_DIR line for each
directory, but make sure these are on consecutive lines otherwise only the
last directory will be accessible. Alternatively separate each directory
with a space on a single UTL_FILE_DIR line.

Directory Permissions

Directories on FAT partitions

FAT does not support file or directory permissions.

Directories on NTFS partitions

When an oracle instance is created the services that support it are setup
to Log On As the SYSTEM (or operating system) account. For UTL_FILE's
procedures to access directories that do not have access granted to
everyone they must have "Change" privileges granted to the SYSTEM account.
If the OracleServiceXXXX service has been altered to log on as another
account, it is this account that must be given access to the directories.

Accessing Network Directories

Netware Networked Directories

For this to work ensure that the account that started the Oracle instance
has a mapped network drive connected using a fully qualified username/password
with the same drive letter and path as is specified by UTL_FILE_DIR.

NT Networked Directories

When an oracle instance is created the services that support it are setup
to "Log On As" the SYSTEM (or operating system) account, this account has
very few privileges and no access to NT Domains. To access another NT machine
the OracleServiceXXXX must be setup to logon to the appropriate NT Domain as
a user who has access to the required location for UTL_FILE.

To change the default logon for the Oracle services, go to :

  -> Control Panel
     -> Services
        -> OracleServiceXXXX (where XXXX is the instance name)
           -> Startup
              -> Log On As

Choose the "This Account" radio button, then complete the appropriate
domain login information. Repeat this procedure for the TNS Listener :

  -> Control Panel
     -> Services
        -> OracleXXXXTNSListener (where XXXX is the Oracle Home Name)
           -> Startup
              -> Log On As

If the listener is not configured to start as the same user as the Oracle
Service user will fail to connect with "ORA-12500 TNS:listener failed to start
a dedicated server process". Once the services have been setup as a user with
the appropriate privileges the are two options for setting UTL_FILE_DIR :

a. Mapped Drive : To use a mapped drive, the user that the service starts as
must have setup a drive to match UTL_FILE_DIR and be logged onto the server
when UTL_FILE is in use.

b. Universal Naming Convention : UNC is preferable to Mapped Drives because
it does not require anyone to be logged on and UTL_FILE_DIR should be set to
a name in the form :

  \\\<machine name>\<share name>\<path>


  "\\<machine name>\<share name>\<path>"

Author Comment

ID: 22925171
Dear schwertner:

is it possible to set e.g. UTL_FILE_DIR="<server name> or <ip address>\LOG DIR"?

as my files are located on unix platform on different machine then that where is residing my db instance.
LVL 48

Accepted Solution

schwertner earned 1500 total points
ID: 22928256
This is a Unix related question.
I am sure that the answer is "yes",
but you have to ask how on the unix thread.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question