O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Expert

24-29.

What information is displayed on the screen when you run this code (assume that you have UTL_FILE privileges on the c:\temp directory):

DECLARE
   fid UTL_FILE.FILE_TYPE :=
      UTL_FILE.FOPEN ('c:\temp', 'new.txt', 'W');
   line VARCHAR2(2000);
BEGIN
   fid.id := NULL;
   IF UTL_FILE.IS_OPEN (fid)
   THEN
      DBMS_OUTPUT.PUT_LINE ('Seems open to me...');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Who closed my file?');
   END IF;
END;
/

24-30.

Create a procedure that allows you to write lines of arbitrary length (even exceeding the maximum allowed in UTL_FILE) to a file.

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: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required