PL/SQL Tip of the Month

From: <info_at_revealnet.com>
Date: 1998/04/06
Message-ID: <6gb5ke$qus$1_at_nnrp1.dejanews.com>#1/1

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:

1. 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.
2. 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 CEST