Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
24-29.	You see the following results: SQL> @isopen.tst Who closed my file? In other words, even though the file is actually open, UTL_FILE acts as if it is closed. How to explain this strange behavior? Simple: the UTL_FILE.IS_OPEN function is not all that it appears to be. This program does not (as of Oracle8i Release 8.1, in any case) actually check with the operating system concerning the status of this file. It merely checks the value of the id field of the UTL_FILE.FILE_TYPE record (in the above block, fid); if that field is NOT NULL, the file is deemed to be open.
Q:
24-30.	This is a trick, and a tricky question. There really isn’t a way to get around the maximum-length limitation in UTL_FILE (1023 bytes prior to 8.0.5, 32K afterwards—at which point this idea will probably become irrelevant). Instead, you can write out chunks of the string within the allowable size, and then tack on a “continuation character” that indicates the line is continued and needs to be merged. Here is one possible implementation that allows you to specify the continuation character and also the maximum allowable line size: /* Filename on web page: putline.sp */ CREATE OR REPLACE PROCEDURE put_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2, continchar_in IN VARCHAR2 := '-', max_line_size_in IN INTEGER := 1023) IS v_line VARCHAR2(32767); v_linelen CONSTANT INTEGER := LENGTH (line); v_start ...