April's Tip of the Month
From the PL/SQL Pipeline, a free internet community for Oracle developers
hosted by Steven Feuerstein and sponsored by RevealNet. Visit the PL/SQL
Pipeline at http://www.revealnet.com/pipeline.htm
UTL_FILE Limitations
If you are running Oracle 7.3 or above, you can take advantage of the
UTL_FILE package to read from and write to operating system files (located on
the database server machine). Just keep in mind the following limitations:
- The maximum size of a line that can be read/written through UTL_FILE is
1023 bytes (or thereabouts) - until you get to Oracle8, at which time the
limitation is raised to 32K. If you try to read/write a longer chunk of bits,
UTL_FILE will raise its WRITE_ERROR or READ_ERROR exceptions.
- You can only open a maximum of 10 files per Oracle session using
UTL_FILE.FOPEN. If you try to open more than that, UTL_FILE will raise its
INVALID_OPERATION error. Oracle is aware of the problem and working on a fix.
I do not have a confirmed version in which the limitation will be lifted.
Remember that this is a per user limit. If you have twenty users and they
each run an app that opens 6 files, you will have 120 files and that is OK
(as long as it's OK with your operating system and/or Bill Gates).
Here is the script I wrote to determine the open files limit:
DECLARE
files plvtab.file_table;
fnum INTEGER := 1;
BEGIN
LOOP
files(fnum) := UTL_FILE.FOPEN ('c:\temp', 'temp' ||
fnum || '.dat', 'W');
fnum := fnum + 1;
END LOOP;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN p.l (PLVfile.c_invalid_path);
WHEN UTL_FILE.INVALID_MODE
THEN p.l (PLVfile.c_invalid_mode);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN p.l (PLVfile.c_invalid_filehandle);
WHEN UTL_FILE.INVALID_OPERATION
THEN p.l (PLVfile.c_invalid_operation);
WHEN UTL_FILE.READ_ERROR
THEN p.l (PLVfile.c_read_error);
WHEN UTL_FILE.WRITE_ERROR
THEN p.l (PLVfile.c_write_error);
WHEN UTL_FILE.INTERNAL_ERROR
THEN p.l (PLVfile.c_internal_error);
WHEN OTHERS
THEN p.l (SQLCODE);
end;
/
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Mon Apr 06 1998 - 00:00:00 CDT
