Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    144

    Utl_file with zero bytes

    Hi All,

    I have the below requirement:

    I have an oracle piece of code which access (open the file in 'Read' Mode) a flat file and read the file to insert data into Oracle table. My requirement is, if the flat file size is zero bytes than do not proceed further.


    Thanks with Regards,
    JD

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,622
    You won't know it contains 0 bytes until after you try to actually read it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Feb 2006
    Posts
    172
    Quote Originally Posted by jayanta_deb View Post
    if the flat file size is zero bytes than do not proceed further.
    In your exception section check for "NO_DATA_FOUND", The below is clip from:Oracle® Database PL/SQL Packages and Types Reference GET_LINE Procedure

    This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. Text is read up to, but not including, the line terminator, or up to the end of the file, or up to the end of the len parameter. It cannot exceed the max_linesize specified in FOPEN.
    Syntax
    UTL_FILE.GET_LINE ( file IN FILE_TYPE, buffer OUT VARCHAR2, len IN PLS_INTEGER DEFAULT NULL); Parameters
    Table 168-14 GET_LINE Procedure Parameters
    Parameters Description file
    Active file handle returned by an FOPEN call.
    The file must be open for reading (mode r); otherwise an INVALID_OPERATION exception is raised.
    buffer
    Data buffer to receive the line read from the file.
    len
    The number of bytes read from the file. Default is NULL. If NULL, Oracle supplies the value of max_linesize.


    Usage Notes
    If the line does not fit in the buffer, a VALUE_ERROR exception is raised. If no text was read due to end of file, the NO_DATA_FOUND exception is raised. If the file is opened for byte mode operations, the INVALID_OPERATION exception is raised.
    Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.
    The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN.If unspecified, Oracle supplies a default value of 1024. See also "GET_LINE_NCHAR Procedure".
    Exceptions
    INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR NO_DATA_FOUND VALUE_ERROR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •