Re: UTL_FILE.PUT_LINE

On Mar 5, 12:14 pm, DA Morgan <damor..._at_psoug.org> wrote:
> fitzjarr..._at_cox.net wrote:
> > On Mar 4, 1:18 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> >> Does UTL_FILE.PUT_LINE have a limitation on the length of the string
> >> you are printing to file?
>
> >> I have a variable declares, VARCHAR2(4000).  I print it to a log
> >> file.  However, I have figured out that if I print more than 1000
> >> characters, the procedure just stops......
>
> >> Anyone?
>
> > Had you taken the time to read the documentation on UTL_FILE you'd
> > realize that the FOPEN call accepts a MAX_LINESIZE parameter, which,
> > if not supplied, defaults to 1024:
>
> >http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file...
>
> > Examining Example 2 on that page explains much.
>
> > The problem, therefore, isn't with the PUT_LINE call but in how you
> > opened the file.
>
> > David Fitzjarrell
>
> Interesting that the doc specifies 1024 because the package header reads
> as follows:
>
> utl_file.fopen(
> file_location IN VARCHAR2,
> file_name     IN VARCHAR2,
> open_mode     IN VARCHAR2,
> max_linesize  IN BINARY_INTEGER DEFAULT NULL)
> RETURN file_type;
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Actually the comments in that header read:

  /*
  ** FOPEN - open file
  **
  ** As of 8.0.6, you can have a maximum of 50 files open

  **
  ** As of 9.0.2, UTL_FILE allows file system access for directories
  ** created as database objects.  See the CREATE DIRECTORY command.
  ** Directory object names are case sensitive and must match exactly
  ** the NAME string in ALL_DIRECTORIES.  The LOCATION parameter may

• either a directory string from the UTL_FILE_DIR init.ora parameter
• or a directory object name. **
• IN
• location - directory location of file
• filename - file name (including extention)
• open_mode - open mode ('r', 'w', 'a' 'rb', 'wb', 'ab')
• max_linesize - maximum number of characters per line, including the
• newline character, for this file.
• Valid values are 1 through 32767 and NULL. A NULL
• value for max_linesize indicates that UTL_FILE should
• calculate an operating system specific value at runtime.
• RETURN
• file_type handle to open file
• EXCEPTIONS
• invalid_path - file location or name was invalid
• invalid_mode - the open_mode string was invalid
• invalid_operation - file could not be opened as requested
• invalid_maxlinesize - specified max_linesize is too large or too small
• access_denied - access to the directory object is denied */ FUNCTION fopen(location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT NULL) RETURN file_type; PRAGMA RESTRICT_REFERENCES(fopen, WNDS, RNDS, TRUST);

And, apparently, that value for UNIX systems is 1024 (Example 2 is for a UNIX operating system).

David Fitzjarrell Received on Wed Mar 05 2008 - 13:26:31 CST