2. April 2009

In Windows, SQL und PLSQL: "oerr" für alle!

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 ...

Keine Kommentare:

Beliebte Postings