Oracle Utilities - Using Hidden
Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and
More
Creating Custom Message Files
The good thing about oerr is that it is always there to use. Similar to
the VI text editor in UNIX , oerr will be on every Oracle UNIX server.
The challenge is to augment the supplied Oracle messages with your own,
tailored to your specific environment.
Rather than editing the message files provided by Oracle, it is better
to create new ones. This way, the message files are insulated from
Oracle releases and the contents retained, without losing any new Oracle
text. For instance, if the text in the Oracle supplied file was
modified, what would happen when the next release of Oracle provides
more text on the same error? That new information would be lost, and the
changes would need to be made again in the new supplied message files.
A much better option is to create a customized facility and component.
That way, any Oracle expertise is retained in the Oracle supplied
message files, while customized, specific information is added to the
new message files. Then, instead of executing oerr and receiving these
weak results:
$ oerr ora 00942
00942, 00000, "table or view does not exist"
// *Cause:
// *Action:
The following oerr command could be used to provide output from the
customized message file:
$ oerr mycompany 00942
00942, 00000, "table or view does not exist"
// *Cause: A lot of things can cause this error, however, the
// most frequent one in our shop is trying to access
// a table or view in another users schema. You do
// not have access to that schema and therefore Oracle
// says it doesn’t exist for you to see.
// *Action: Try fully qualifying the table or view name
// (scott.table1). Verify that you have privileges to the
// object you’re trying to access.
Notice the only difference is the new facility “mycompany”, which points
to its own message file. A customized message file can be created in
three easy steps:
1. Add the facility to the facility.lis file.
2. Create a directory that contains the new message file.
3. Create the actual message file.
The above is an excerpt from Oracle Utilities - Using
Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof
and More by Rampant TechPress.
For more details on Oracle
utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher
for 30% off directly from
Rampant TechPress.
