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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
