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

Posted on 2008-11-05
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','','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
    LVL 47

    Expert Comment


    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.

    e.g. UTL_FILE_DIR="E:\LOG DIR".

    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

    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 47

    Accepted Solution

    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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    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…
    Via a live example, show how to take different types of Oracle backups using RMAN.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now