English title: Windows, SQL and PL/SQL: "oerr" for everyone!
Wenn ich bei einem ORA-Fehler nur die Fehlernummer habe, benutze ich (auf Unix/Linux) gerne das oerr-Utility, um mir die Meldung anzeigen zu lassen (die meisten von euch kennen das Tool sicherlich) ...
When I encounter an ORA error having only the error number I like using
the oerr utility to get the message and some additional information (the most of you might know this tool) ....
$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
// First, check for network problems and review the SQL*Net setup.
// Also, look in the alert.log file for any errors. Finally, test to
// see whether the server process is dead and whether a trace file
// was generated at failure time.
Schade nur, dass man am UNIX angemeldet sein muss und das das Tool auf Windows
gar nicht zur Verfügung steht ... Schöner wäre es ja, wenn es auf allen Plattformen eine SQL-Funktion
oerr gäbe, mit dem man sich die Informationen aus der SQL-Ebene heraus
anzeigen lassen könnte.
But the utility is only available on UNIX or Linux systems, and
you have to be logged into the shell in order to use it. But would be much nicer when to tool would
reside in the database and could be accessed with a SQL query.
Dazu zuerst ein paar Gedanken zur Arbeitsweise des oerr-Tools. Alle Fehlermeldungen
sind in Dateien im Verzeichnisbaum des ORACLE_HOME abgelegt. Es gibt binäre Dateien
mit der Endung msb und Klartextdateien mit der Endung msg - letztere nur auf
Unix/Linux. Die Dateien befinden sich immer in einem Verzeichnis mesg und haben
das Namensschema "{facility}us.msg". Die ORA-Fehlermeldungen sind also
im Verzeichnis {...}/mesg in der Datei oraus.msg. Eine Liste aller
"facilities" ist in der Datei $ORACLE_HOME/lib/facilities.lis zu finden. Das
oerr-Tool liest diese Dateien; öffnet die richtige msg-Datei und filtert mit
einem awk-Befehl die gewünschte Fehlermeldung aus.
First some thoughts about how the oerr utility
does work: All error messages are in files distributed over the $ORACLE_HOME
directory tree. There are binary files with the msb extension and clear text
files with the msg extension. The latter ones are only available on Unix/Linux systems.
All files are in folders named mesg in various subfolders under $ORACLE_HOME. The
filename is always "{facility}us.msg" - so the ORA error messages reside in
the file oraus.msg. A listing of all mesg directories containing message
files is in the file $ORACLE_HOME/lib/facilities.lis. The oerr utility
parses this file, opens the correct message file and filters the desired information
with a simple awk command.
Ein erster Gedanke von mir war, die Arbeitsweise von oerr mit
Java in der Datenbank nachzubilden, also anhand der facility und der
Fehlernummer zuerst in der facilities.lis nachzusehen, dann die
richtige Datei zu öffnen und die gewünschte Fehlermeldung darin zu suchen. Damit
hätte man die Funktionalität in die Datenbank genommen - sie würde aber
nach wie vor nicht auf Windows bereitstehen ... nicht so gut ...
My first thought was to mimic the semantics
of the oerr tool using java in the database: open the facilties.lis file,
determine the correct subdirectory, open the correct msg file and lookup
the information for the desired error message. This would make the functionality
available to the SQL layer. But it would still be restricted to UNIX/Linux systems.
Also habe ich mich anders entschieden: Man könnte die Dateien ja auch komplett
durchparsen und die Inhalte in eine Datenbanktabelle speichern - die könnte man
anschließend mit Export/Import in eine beliebige Datenbank übertragen und hätte
die oerr-Funktion damit in jeder beliebigen Datenbank.
So I came to another idea: The files are being
be completely and the error message information is being stored in a database table.
This table can then be moved to another (windows) database using export/import. This
would enable the oerr functionality in every database, independent of the platform.
Also zunächst als SYS das folgende SQL-Skript laufen lassen (ein andere User geht
natürlich auch). Es erzeugt die
Tabelle ERROR_MESSAGES und eine Funktion OERR zum Abrufen der
Informationen. Zum Abschluß nur noch ein PUBLIC SYNONYM einrichten und
EXECUTE-Privilegien an PUBLIC geben ...
First install as SYS the following SQL script (using another
database user is also possible). It
creates the table ERROR_MESSAGES and a PL/SQL function OERR to
retrieve the information for a particular error message. Finally create a public synonym
and grant execute privileges to public.
drop table error_messages purge
/
drop public synonym oerr
/
create table error_messages (
facility varchar2(20),
errornum number(5),
message varchar2(1000),
description varchar2(4000),
constraint pk_error_messages primary key (facility, errornum)
)
/
create or replace function oerr (
p_facility error_messages.facility%TYPE,
p_errornum error_messages.errornum%TYPE
) return varchar2 is
v_message varchar2(4000);
begin
begin
select
upper(facility) || '-' || lpad(to_char(errornum),5,'0') || ':' || message || chr(10) || description
into v_message
from error_messages
where facility = p_facility and errornum = p_errornum;
exception
when NO_DATA_FOUND then
v_message := 'Error Message "' || upper(p_facility) || '-' || lpad(to_char(p_errornum), 5, '0') || '" not found.';
end;
return v_message;
end;
/
sho err
grant execute on oerr to public
/
create public synonym oerr for oerr
/
Und diese Tabelle muss man nun mit den Fehlermeldungen füllen. Das besorgt das folgende
Java-Programm. Erst mal kopieren (sind ein paar Codezeilen) und in eine Datei OerrInstall.java speichern.
Now the table has to be populated. This is done by the
following java program. Copy (it has some lines of code) and store it into a file named OerrInstall.java.
import java.util.StringTokenizer;
import java.io.File;
import java.io.FileReader;
import java.io.FileInputStream;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.Properties;
import java.sql.*;
public class OerrInstall {
private String sOracleHome = null;
private String sUsername = null;
private String sPassword = null;
private String sConnectionString = null;
private boolean bSysdba = false;
private Connection con = null;
private PreparedStatement pstmt = null;
public static void main (String args[]) throws Exception {
String sUsername = null;
String sPassword = null;
String sConnectionString = null;
String sOracleHome = null;
boolean bSysdba = false;
for (int i=0;i<args.length;i++) {
if (args[i].startsWith("user")) {
sUsername = args[i].substring(args[i].indexOf("=") + 1);
}
if (args[i].startsWith("password")) {
sPassword = args[i].substring(args[i].indexOf("=") + 1);
}
if (args[i].startsWith("connstr")) {
sConnectionString = args[i].substring(args[i].indexOf("=") + 1);
}
if (args[i].equals("sysdba")) {
bSysdba = true;
}
if (args[i].startsWith("oraclehome")) {
sOracleHome = args[i].substring(args[i].indexOf("=") + 1);
}
}
if (sUsername == null || sPassword == null || sConnectionString == null || sOracleHome == null) {
System.out.println("Usage example:");
System.out.println("$ java OerrInstall user=sys password=oracle sysdba oraclehome=[orahome] connstr=localhost:1521:orcl");
System.exit(0);
}
OerrInstall oi = new OerrInstall(sUsername, sPassword, sConnectionString, bSysdba, sOracleHome);
}
public OerrInstall(
String psUsername,
String psPassword,
String psConnectionString,
boolean pbSysdba,
String psOracleHome
) throws Exception {
sUsername = psUsername;
sPassword = psPassword;
sConnectionString = psConnectionString;
bSysdba = pbSysdba;
sOracleHome = psOracleHome;
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Properties p = new Properties();
p.put("user", sUsername);
p.put("password", sPassword);
if (bSysdba) {
p.put("internal_logon", "sysdba");
}
con = DriverManager.getConnection("jdbc:oracle:thin:@" + sConnectionString, p);
con.setAutoCommit(false);
pstmt = con.prepareStatement(
"insert into error_messages (" +
" facility, errornum, message, description" +
") values (" +
" ?,?,?,?" +
")"
);
doit(con);
pstmt.close();
con.commit();
con.close();
}
private void doit(Connection con) throws Exception {
boolean bMessageInProcess = false;
String sFacility = null;
int iMessageNum = 0;
int iNewMessageNum = 0;
String sMessage = null;
StringBuffer sDescription = new StringBuffer();
BufferedReader oFacReader = null;
File oMsgFile = null;
BufferedReader oMsgReader = null;
String sSubDir = null;
String sLine = null;
StringTokenizer st = null;
int iMessageCount = 0;
/* outer loop: Read the facility list ... */
oFacReader = new BufferedReader(
new FileReader(sOracleHome + File.separator + "lib" + File.separator + "facility.lis")
);
while ( (sLine = oFacReader.readLine()) != null) {
if (! (sLine.startsWith("#") || sLine.length() == 0)) {
st = new StringTokenizer(sLine, ":");
sFacility = st.nextToken();
sSubDir = st.nextToken();
oMsgFile = new File(
sOracleHome + File.separator +
sSubDir + File.separator +
"mesg" + File.separator +
sFacility+"us.msg"
);
if (oMsgFile.exists()) {
System.out.print("Entering message file for facility \"" + sFacility.toUpperCase() + "\" ... ");
oMsgReader = new BufferedReader(
new InputStreamReader(
new FileInputStream(
sOracleHome + File.separator +
sSubDir + File.separator +
"mesg" + File.separator +
sFacility+"us.msg"
),
"us-ascii"
)
);
/* inner loop: parse the message file ... */
bMessageInProcess = false;
iMessageCount = 0;
while ( (sLine = oMsgReader.readLine()) != null) {
try {
iNewMessageNum = Integer.parseInt(sLine.substring(0,sLine.indexOf(",")));
if (bMessageInProcess) {
storeMessage(sFacility, iMessageNum, sMessage, sDescription.toString());
}
iMessageNum = iNewMessageNum;
bMessageInProcess = true;
sDescription.setLength(0);
iMessageCount++;
st = new StringTokenizer(sLine, ",");
st.nextToken();
st.nextToken();
sMessage = st.nextToken();
} catch (Exception e) {
if (sLine.startsWith("//")) {
if (bMessageInProcess) {
sDescription.append(sLine).append("\n");
}
} else {
if (bMessageInProcess) {
bMessageInProcess = false;
storeMessage(sFacility, iMessageNum, sMessage, sDescription.toString());
}
}
}
}
oMsgReader.close();
if (bMessageInProcess) {
storeMessage(sFacility, iMessageNum, sMessage, sDescription.toString());
iMessageCount++;
}
System.out.println( iMessageCount + " messages.");
}
}
}
oFacReader.close();
System.out.println("\nFinished.");
}
private void storeMessage(
String sFacility,
int iMessageNum,
String sMessage,
String sDescription
) throws Exception {
pstmt.setString(1, sFacility);
pstmt.setInt(2, iMessageNum);
pstmt.setString(3, sMessage);
pstmt.setString(4, sDescription);
pstmt.execute();
}
}
Dann kompilieren (die Klassen des Oracle-JDBC-Treibers müssen im CLASSPATH sein) ...
Now compile it (the Oracle jdbc classes must be present in the java CLASSPATH)
$ javac OerrInstall.java
Und aufrufen ... Der Aufruf muss auf einem Unix/Linux-System erfolgen, denn nur
dort sind die Klartextdateien, die geparst werden, vorhanden. Die Zieldatenbank, die
mit dem Parameter connstr festgelegt wird und in die die
Daten gespeichert werden, kann aber durchaus eine entfernte Windows-Datenbank sein ...
Start the program. Since the clear text message files
which are being parsed are only present on UNIX/Linux system, The java program must be executed
there. But the target database which is determined by the connstr parameter might
be any other database (also on Windows platforms).
$ java OerrInstall user=SYS \
password=[syspw] \
sysdba
connstr=[host]:[port]:[orasid] \
oraclehome=[absoluter pfad zum ORACLE_HOME auf dem Unix-System]
Auch hier muss nicht unbedingt SYS verwendet werden, ein anderer User geht auch. Es muss
aber der sein, unter dem vorher das SQL-Skript eingespielt wurde. Lasst dann einfach
den Parameter sysdba weg. Ihr seht dann etwa folgende
Ausgabe ..
You also don't have to use SYS here. But you might
choose the same account into which you installed the SQL script before. When you don't
use SYS omit the parameter sysdba. You'll see the following output ...
Entering message file for facility "AUD" ... 19 messages.
Entering message file for facility "CLSR" ... 58 messages.
:
:
Entering message file for facility "UDI" ... 157 messages.
Entering message file for facility "VID" ... 13 messages.
Finished.
Und das war's ... Nun einfach auf die Datenbank, die mit connstr angegeben wurde, verbinden
und mit SQL*Plus ausprobieren ...
That's it. Now connect to the database which you have specified
in the connstr parameter and try the new function ...
SQL> select oerr('ora',31001) from duaL;
OERR('ORA',31001)
--------------------------------------------------------------------------------
ORA-31001: "Invalid resource handle or path name \"%s\""
// *Cause: An invalid resource handle or path name was passed to
// the XDB hierarchical resolver.
// *Action: Pass a valid resouce handle or path name to the hierarchical
// resolver.
Viel Spaß damit ...
Have fun ...
