Solved

execute stored procedure to UTL_FILE FOPEN via db link

Posted on 2006-11-03
8
3,143 Views
Last Modified: 2008-01-09
I login sqlplus on db_server_local on unix_local to run a remote stored procedure (via db link) on db_server_remote on unix_remote. The remote stored procedure uses UTL_FILE FOPEN.  Does the procedure open the file on unix_local or unix_remote?  Thanks.


0
Comment
Question by:ewang1205
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Expert Comment

by:cgilbert78fr
ID: 17866471
Hi !

What is the syntax of the call to the remote procedure ?

Regards
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 50 total points
ID: 17866498
i think it should be in unix_remote only. But i have never done it or tested it.

Thanks
0
 

Author Comment

by:ewang1205
ID: 17866558
Any other expert opinion?  Thanks.
0
Independent Software Vendors: 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!

 
LVL 48

Accepted Solution

by:
schwertner earned 200 total points
ID: 17866565
Oracle controls the access to files the programmer does via the package UTL_FILE.
There are some prerequistes the DBA has to do before UTL_FILE can access files.


Server security for PL/SQL file I/O consists of a restriction on
the directories that can be accessed.
Accessible directories must be specified in the
instance parameter initialization file (INIT.ORA or SPFILE).

Specify the accessible directories for the UTL_FILE
functions in the initialization file
using the UTL_FILE_DIR parameter.
For example:
UTL_FILE_DIR = <directory name>

Starting from 9i you can use Oracle directories instead:

create or replace directory XMLDIR as 'E:\ICW_TASKS\XML\SOURCE\';
grant read on directory xmldir to my_user with grant option;


The remote stored procedure runs using the access rights assigned to the
owner of the procedure and this owner is remote.
So the remote procedure will see only the directory and files visible from the remote server.

The question if the local directory is visible from the remote server is an OS question.



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>

  or

  "\\<machine name>\<share name>\<path>"
0
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 100 total points
ID: 17866627
db_server_remote - just made an expariment
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 60 total points
ID: 17866740
The file will be opened in the machine where the database is hosted.
I.e. if you are running the procedure using a db_link: the file will be opened in the machine where the database pointed thru the db link is residing.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 90 total points
ID: 17868981
If the procedure is a remote procedures that you run via a database link, then the file that utl_file creates will also be on the remote server (the server where the procedure is a local procedure).
0
 

Author Comment

by:ewang1205
ID: 17869082
Thanks for the great help!  Will split.
0

Featured Post

Independent Software Vendors: 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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…

733 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