[20250712]建立完善oerrz.sql脚本.txt

[20250712]建立完善oerrz.sql脚本.txt --//重新编写oerrz.sql脚本，使用更加灵活。 --//我以前使用bash shell写的如下。 $ cat ~/bin/oerrx #! /bin/bash a=`echo $1 | cut -d"-" -f1` b=`echo $1 | cut -d"-" -f2` oerr $a $b --//需要在sqlplus查看时执行： SCOTT@book01p> host oerrx ora-1 00001, 00000, "unique constraint (%s.%s) violated" // *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. //         For Trusted Oracle configured in DBMS MAC mode, you may see //         this message if a duplicate entry exists at a different level. // *Action: Either remove the unique restriction or do not insert the key. --//tpt的脚本也一个查询oerr.sql的脚本，直接执行的是 exec dbms_output.put_line(sqlerrm(-&1)) --//缺点仅仅查询ora系列的错误。 --//我自己改写如下，查询其他错误也更加灵活。 $ cat oerrz.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   oerrz.sql -- Purpose:     oerr functionality - list description for and ORA- or other error code --              The data comes from $ORACLE_HOME/rdbms/mesg/oraus.msb file --              which is a binary compiled version of $ORACLE_HOME/rdbms/mesg/oraus.msg file -- -- Author:      lfree -- -- Usage:       @ oerrz <error_code> --              @ oerrz ora-1403 --              @ oerrz 1403 --              default ora- error -------------------------------------------------------------------------------- column error_s new_value error_s column error_n new_value error_n set term off select nvl(substr('&1',1,instr('&&1','-')-1),'ora') error_s,substr('&1',instr('&&1','-')+1) error_n from dual ; set term on prompt host oerr &error_s &error_n --//测试例子： SYS@book> @ oerrz 1 00001, 00000, "unique constraint (%s.%s) violated" // *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. //         For Trusted Oracle configured in DBMS MAC mode, you may see //         this message if a duplicate entry exists at a different level. // *Action: Either remove the unique restriction or do not insert the key. SYS@book> @ oerrz ora-1403 01403, 00000, "no data found" // *Cause: No data was found from the objects. // *Action: There was no data from the objects which may be due to end of fetch. SYS@book> @ oerrz tns-12560 12560, 00000, "TNS:protocol adapter error" // *Cause: A generic protocol adapter error occurred. // *Action: Check addresses used for proper protocol specification. Before // reporting this error, look at the error stack and check for lower level // transport errors. For further details, turn on tracing and reexecute the // operation. Turn off tracing when the operation is complete. --//顺便修改oerrx的bash shell脚本为oerrz: $ cat oerrz #! /bin/bash pos=$(expr index $1 "-") len=${#1} if [[ $pos == 0 ]];then     a=ora     b=$1 else     #a=$(expr substr "$1" 1 $(( pos -1 )))     #b=$(expr substr "$1" $(( pos +1 )) $len )     a=${1:0: $(( pos -1 ))}     b=${1:$(( pos  ))} fi echo oerr $a $b echo