If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Utl_file with zero bytes

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,598
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On