Re: Using UTL_FILE on Oracle8 under NT

From: ykhogaly <ykhogaly_at_us.oracle.com>
Date: Sun, 16 Jan 2000 11:05:08 -0700
Message-ID: <85t18c$gv9$1@inet16.us.oracle.com>

Steps to verify setup for UTIL_FILE package on Windows NT:

---

Revision Number: 1
Product: PL/SQL
Product Version: 7.x & 8.x
Platform: Windows NT
Platform Version: 4.0
Abstract: Steps to verify setup for the UTL_FILE package on NT.

Steps to verify correct setup for the UTL_FILE package.

The UTL_FILE package is used to add File I/O functionality to PL/SQL. All I/O
operations performed by UTL_FILE are performed on the server side. (Note: Client side file I/O operations can be performed using the TEXT_IO package.) With the UTL_FILE package performing operations on the server, there is always
the important issue of security. The security is controlled by the UTL_FILE_DIR parameter which gets added to the database initialization file. This must be done before any file I/O can be performed. If not, you should get
an "ORA-20100: Invalid Path" on the FOPEN function.

The security and directory accessibility are controlled by the UTL_FILE_DIR parameter. If the INIT.ORA file contains UTL_FILE_DIR = * then database permissions are disable and all files and directories are accessible to all users. Usually a directory is created specifically for file I/O. For example, if directory FILEIO was created on the
D: drive then the UTL_FILE_DIR parameter would be setup in this manner: UTL_FILE_DIR = D:\FILEIO Suggested Trouble shooting techniques

---

If after adding the UTL_FILE_DIR parameter to the INIT.ORA file you are still
experiencing a problem, there are a couple of suggestions which should help remedy the situation.

First, verify that you have bounced the database after changing the INIT.ORA file. Remember that in order to have any of the changes to the INIT file invoked, you must stop and restart the database.

Second, keep in mind that INIT.ORA is not always the name of the initialization file. The correct parameter file can be found in the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\PFILE_ORCL = "<path>\<filename>"

Listed below is an excellent sample to run to verify that the UTL_FILE_DIR is
setup correctly.

DECLARE
   fHandle UTL_FILE.FILE_TYPE;
   vText varchar2(10);
BEGIN
   fHandle := UTL_FILE.FOPEN('c:\','utlfile.txt','w');    vText := 'TEST'; UTL_FILE.PUTF(fHandle,vText);    UTL_FILE.FCLOSE(fHandle);
EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN

   RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
   RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_OPERATION then
   RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
WHEN UTL_FILE.INVALID_FILEHANDLE then
   RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
WHEN UTL_FILE.WRITE_ERROR then
   RAISE_APPLICATION_ERROR(-20104,'Write Error');
WHEN UTL_FILE.READ_ERROR then
   RAISE_APPLICATION_ERROR(-20105,'Read Error');
WHEN UTL_FILE.INTERNAL_ERROR then
   RAISE_APPLICATION_ERROR(-20106,'Internal Error');

WHEN OTHERS THEN
   UTL_FILE.FCLOSE(fHandle);
END; If the above 2 steps do not work, try the following steps:

1. Enter UTL_FILE_DIR = * into the parameter file.
2. Stop and start the database.
3. Try to run the sample.

   If it fails then continue with step 4 4. Stop the database, rename the init file then start the database.

   If the database does not start up then the correct file was    updated and the problem may be due to a system configuration.    If the database starts up, this means the wrong init was updated.    Check the registry to find the correct parameter file. 5. Once the correct init file is found, add the UTL_FILE_DIR

   parameter then try to restart the db and run the sample. 6. Verify that the status for the UTL_FILE package is VALID. Do this

   using either Schema Manager or by entering the following SQL:     SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'UTL_FILE'; 7. If this still does not work, I would suggest running the sample as System.

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

"Ted" <ted_at_tEDV.de> wrote in message news:85sqgc$n1l$1_at_passat.ndh.net...
> Hi there,
>
> I have a problem using fopen from utl_file with drive letters
> under Oracle 8.0.5 /NT.
>
> I can only open files located on the drive Oracle is installed (D:)
> My init.ora contains a "UTL_FILE_DIR = *"
> Opening a file with "UTL_FILE.fopen('\MyDir', 'MyFile', 'r');"
> works just fine, but "UTL_FILE.fopen('C:\MyDir', 'MyFile', 'r');"
> does not work. It is written in some books that this SHOULD work.
>
> What is wrong here?
> (The Oracle documentation tells nothing about opening files with drive
> drive letters under NT)
>
>
> Thanks in advance,
> bye,
> Ted.
>
>
Received on Sun Jan 16 2000 - 12:05:08 CST