Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

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: ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page