Oracle Error Messages to a database table.

From: William Rogge <William_Rogge_at_voltek.com>
Date: Wed, 22 Nov 2000 14:42:13 -0500
Message-Id: <10688.122756@fatcity.com>

I had time to play around and was able to accomplish this little task. I hope it is of value to you.

First, I am on a UNIX platform, not WINDOWS.

I was able to use a combination of a shell script (calling sed), an awk script, a sql to create the table and sqlload to populate the table.

This is a long message to include all of the files/command lines, but once the data is in the table, quick references to the data is worthwhile.

OK, here goes.

Oracles .msg file is an odd structure. They liked to mix apostrophes and quotation marks which made sqlload choke. the general structure is as follows:

00000, 00000, "this is the error"

// *Cause:   This is the cause of the error
//           which may run onto multiple lines
// *Action: This is what the user should do to correct the problem
//          which may also run onto multiple lines.
/   Then they throw in comments like this.

There are cases where they changed their structure, like not giving a Cause or not giving an Action. The awk script accounts for this. They even spelled a few of the keywords wrong in places.

Step 1. Make " to ' conversion (shell script file).
- - - beginning of file

cat $ORACLE_HOME/rdbms/mesg/oraus.msg | sed "s/\"/\'/g" >orig_oraus.msg
- - - end of file

The resultant file is saved for later processing.

Step 2. Take out the comment lines and prepare to import with SQL*Loader. (awk script) file name do_help.awk
- - - beginning of file

BEGIN{li=0; val=0; det=0; cau=0; act=0;} {if (substr($0,1,2) == "//")
  {if (val == 1)
    {if ($0 ~ " *Cause" || $0 ~ " *Casue")

      {printf(",\n//\"");
       det=1;
       cau=1;
       for (i = 3; i <= NF ; ++i)
        {printf("%s ",$i)
        }
      }
     else
      {if ($0 ~ " *Action")
        {if (cau == 0)
          {printf(",\n//\"")
	   cau=1;
          }
         printf("\",\n//\"");
	 det=1;
	 act=1;
         for (i = 3; i <= NF ; ++i)
	  {printf("%s ",$i)
	  }
        }
       else
        {if (det == 1)
	  {printf("\n//");
           for (i = 2; i <= NF ; ++i)
            {if (i>2)
              {printf(" %s",$i)
              }
             else
              {printf("%s",$i)
              }
            }
	  }
        }
      }

    }
  }
 else

   {if (substr($0,1,1) ~ "[0-9]")
     {pl1=substr($0,1,5)
      pl2=substr($0,16,length($0)-16)
      val=1;
      det=0;
      if (li == 0)
       {li=li+1;
        printf("     \"%5s\",\"%s\"", pl1, pl2)
	cau=0;
	act=0;
       }
      else
       {if (cau == 0)
         {printf(",\n//\"")
	 }
        if (act == 0)
         {printf("\",\n//\"")
	 }
        printf("\"\n     \"%5s\",\"%s\"", pl1, pl2)
	cau=0;
	act=0;
       }
     }
    else
     {val=0
     }

   }
}
END {printf("\"\n")}
- - - End of file

command to execute the file conversion is

awk -f do_help.awk <orig_oraus.msg >oraus.msg

Step 3. Create the database table and grant public select access. (sql script) file name cr_help.sql
- - - beginning of file

create table help (err_num     varchar2(7),
                   err_txt     varchar2(200),
                   err_cause   varchar2(2000),
                   err_action  varchar2(2000),
                   primary key (err_num))

/
grant select on help to public
/
- - - end of file

Step 4. Run SQL*Loader with the proper control file. (control file) file name imp_help.control
- - - beginning of file

load data
INFILE 'oraus.msg'
TRUNCATE
CONTINUEIF NEXT (1:2) = '//'
into TABLE help
FIELDS TERMINATED BY ','
(

 err_num      char(5) enclosed by '"',
 err_txt      char enclosed by '"',
 err_cause    char(900) enclosed by '"',
 err_action   char(900) enclosed by '"'

)
- - - end of file

The execution command is:

sqlload userid=user/password control=imp_help.control

I will state, that due to Oracle's inconsistencies, you will most likely get some bad records. The ones I encountered was because Oracle had miss coded some of the 23xxx error numbers by having a '// ' at the beginning of Received on Wed Nov 22 2000 - 13:42:13 CST