[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