24-31. | Write an INSTR-like function for UTL_FILE. It should implement the following header: CREATE OR REPLACE FUNCTION infile
(loc_in IN VARCHAR2,
file_in IN VARCHAR2,
text_in IN VARCHAR2,
occurrence_in IN INTEGER := 1,
start_line_in IN INTEGER := 1,
end_line_in IN INTEGER := 0,
ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER;In other words, find the nth occurrence of the string text_in between lines start_line_in and end_line_in, either matching or ignoring case. Assume that the following rules are obeyed to simplify your solution: The file always contains text. The number of occurrences is positive. The starting line is not negative. The ending line is greater than the starting line.
|
24-32. | Let’s take a look at error handling with the UTL_FILE package. I created this procedure (as well as fileplay2.sp) to play around with files: 
/* Filename on web page: fileplay.sp */
CREATE OR REPLACE PROCEDURE play_with_files
(loc_in IN VARCHAR2,
file_in IN VARCHAR2,
mode_in IN VARCHAR2,
mixed_up IN BOOLEAN := FALSE)
IS
fID UTL_FILE.FILE_TYPE;
line VARCHAR2(32767) := 'fun stuff!';
BEGIN
IF mixed_up AND mode_in = 'R'
THEN
fID := UTL_FILE.FOPEN (loc_in, file_in, 'W'),
ELSIF mixed_up
THEN
fID := NULL;
ELSE
fID := UTL_FILE.FOPEN (loc_in, file_in, mode_in);
END IF;
IF mode_in = 'R'
THEN
UTL_FILE.GET_LINE (fid, line);
DBMS_OUTPUT.PUT_LINE (
'Read from ' || file_in || ' in ' || loc_in);
ELSE
UTL_FILE.PUT_LINE (fid, line);
DBMS_OUTPUT.PUT_LINE (
'Wrote to ' || file_in || ' in ' || loc_in);
END IF;
UTL_FILE.FCLOSE (fid);
END;
/
As you can see, it lets me either write to a file or read from a file. If I specify that I want things “mixed up,” the program tries to write to a read-only file or sets the file handle to NULL. Then I set up the following script to exercise my play_with_files procedure:
/* Filename on web page: fileplay.tst */
SET FEEDBACK OFF
EXEC fcreate ('c: emp', 'newfile.txt'),
EXEC play_with_files ('c: emp', 'newfile.txt', 'R'),
EXEC play_with_files ('c:
osuchdir', 'new.txt', 'R'),
EXEC play_with_files ('c:
osuchdir', 'new.txt', 'X'),
EXEC play_with_files ('c: emp', 'nosuchfile.txt', 'R'),
EXEC play_with_files ('c: emp', 'nosuchfile.txt', 'A'),
EXEC play_with_files (
'c: emp', 'nosuchfile.txt', 'R', mixed_up => TRUE);
EXEC play_with_files (
'c: emp', 'nosuchfile.txt', 'W', mixed_up => TRUE);
EXEC UTL_FILE.FCLOSE_ALL;
HOST DEL c: emp
osuchfile.txt
HOST DEL c: emp
ew.txt The fcreate procedure creates a new file (see the fcreate.sp file on the book’s web page). There is no directory named nosuchdir and no file named nosuchfile.txt. Here is the somewhat abbreviated output from running this script: SQL> @fileplay.tst
ORA-01403: no data found
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06510: PL/SQL: unhandled user-defined exception
Wrote to nosuchfile.txt in c: emp
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06510: PL/SQL: unhandled user-defined exception Not too informative, is it? If I try to read past the end of a file (in this case, read the first line of an empty file), UTL_FILE raises NO_DATA_FOUND. I also find that in Oracle 8.1, if I append to a nonexistent file, I no longer get an error; the script wrote to nosuchfile.txt successfully. But once I get past those conclusions, I can determine little else from the output. Why do I keep seeing the same error message, even though it seems as if I am encountering different errors? |
24-35. | You can use UTL_FILE is to write error information to a file; with files, you don’t have to worry about ROLLBACKs erasing your log entries. Write a package that allows a developer to: Specify the location and name of the error log file, which are maintained as global values in the package and for the session. Write the error code, error message, and program name to the file (with the current date, time, and user supplied by the logging program). The components of the line written to the file are separated by a vertical bar, defined as a constant in the package specification. See each line after it is written to the log, even though the file is not yet closed. Close the log at user request.
Here is the specification for the package; you write the body:
/* Filename on web page: flog.pkg */
CREATE OR REPLACE PACKAGE flog /* File LOG */
IS
delim CONSTANT CHAR(1) := '|';
PROCEDURE setfile (loc IN VARCHAR2, file IN VARCHAR2);
PROCEDURE put_line (
prog IN VARCHAR2, code IN INTEGER, msg IN VARCHAR2);
PROCEDURE close;
END flog;
/ Don’t forget to open for append and catch the error if the file does not yet exist. And when and how often should you open and close the file? |
