Reading Text Files using Oracle PL/SQL and UTL_FILE - Improving it a bit with exception handling (Page 4 of 4 )
In the previous sections, we worked by reading a single line from the text file. Let us consider the following situations:
- The file you are trying to access doesn't exist.
- The file you are trying to access got corrupted.
- You don't have the permission to access the file.
- No lines present in that file.
- Too many chars (say more than 200) present on the first line itself.
And so on. How do we solve such problems? Even though we will not be able to address every possible error, there exist some pre-defined exceptions to handle some of the scenarios. Following are the pre-defined exceptions most frequently used when working with UTL_FILE package:
INVALID_PATH
INVALID_MODE
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
WRITE_ERROR
INTERNAL_ERROR
NO_DATA_FOUND
VALUE_ERROR
INVALID_MAXLINESIZE
For a complete list of exceptions, I suggest you go through the Oracle documentation for the respective version you are using.
If you are new to exception handling, I suggest you read my contributions at
http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Predefined-Exceptions/
http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-User-
defined-Exceptions-Nested-Blocks/
My upcoming article will address further issues like "working with a larger number of lines", "better exception handling", "writing to files" and so on. I may also introduce you to some of the professional ways to deal the same issues. Don't forget to keep an eye on this website for the next article (or simply sign up for a newsletter). Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
