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

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