Home arrow Oracle arrow Page 4 - Reading Text Files using Oracle PL/SQL and UTL_FILE

Improving it a bit with exception handling - Oracle

In this series of articles, we shall concentrate on working with file input/output using Oracle PL/SQL packages. Even though I start with simple examples in the first article of this series, I shall introduce you (in my upcoming articles) to the power of the most advanced techniques using file input/output with Oracle PL/SQL.

TABLE OF CONTENTS:
  1. Reading Text Files using Oracle PL/SQL and UTL_FILE
  2. How to read a text file from a file system and display the contents using PL/SQL
  3. How to read a text file from a file system and display the contents using PL/SQL: discussion
  4. Improving it a bit with exception handling
By: Jagadish Chatarji
Rating: starstarstarstarstar / 129
April 11, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: