RE: how to store orace errors in database

From: lerobe - Lee Robertson <LEROBE_at_acxiom.co.uk>
Date: Thu, 22 Mar 2001 00:23:52 -0800
Message-ID: <F001.002D474A.20010322001936@fatcity.com>

Apologies to the original author (William Rogge) of this mail for copying. I kept this the last time it was on the list.

Regards

Lee

STARTS HERE

---

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 the error number record. After editing these, and rerunning the awk and sqlload, I was able to get 100% import.

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: lerobe - Lee Robertson
  INET: LEROBE_at_acxiom.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Mar 22 2001 - 02:23:52 CST