网上查找的一个解决方式
Oracle Database Tips by Donald BurlesonJuly 13, 2015
Question: Am I getting the ORA-29275 error on a SQL query. How can I find the affected rows and how to I fix the data to remove the ORA-29275 error?
Answer: The oerr utility gives us this information on the ORA-29275 error:
ORA-29275: partial multibyte character
Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.
Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.
The core issue of that the nls_chracterset values for the multibyte character set does not match between either two databases in a distributed query, or a query where the webserver has a different character set. There are several possible fixes for the ORA-29275 error, all involving getting the NLS character sets to match:
1 - write a function to convert each row. Place this function inside a PL/SQL function and call it for each row of the table:
CONVERT(COLUMN NAME,'NLS_CHARACTERSET','NLS_CHARACTERSET');
2 - If you have a webserver, try setting the NLS_LANG environmental variable on the webserver to american_america.al32utf8.
References:
See MOS Note 69518.1 - Storing and Checking Character Codepoints in an UTF8/AL32UTF8 (Unicode) database.
See MOS Note 788156.1 - AL32UTF8 / UTF8 (Unicode) Database Character Set Implications
之前只是指定使用sqlcode 和sqlerrm 返回错误信息，但是不知道如何定位到具体哪一个文件的哪一行。
现在 how-oracle-return-error-line by itlife365.com
如果数据库是10g及以上版本，可以使用dbms_utility.format_error_backtrace()，这个函数能够返回错误行,可以套用以下格式：
create or replace procedure proc_test(参数) is
v_no number;
begin
select no into v_no from zrp;
exception
when others then
dbms_output.put_line('SQL CODE:'||sqlcode||chr(10)||
sqlerrm||chr(10)||
dbms_utility.format_error_backtrace());
end;
这样写的好处是，当程序发成异常，呗exception捕获后，就会打印出错误信息及发生错误的行
基于堆栈的原理,如果是多层调用会逐层提示 ,这样异常就能被正确地传输到最外层程序中
更多内容参考《Oracle dbms_utility 几种返回的错误》
how-oracle-return-error-line by itlife365.com oracle dbms_utility error
oracle自动函数解决oracle如何捕获存储过程报错是哪一行 --end by itlife365.com
通常exception when others then 存储过程中会报错并异常退出，如何记录下日志并继续执行呢？
在exception后面添加null ;即可；
---how-continue-when-exception-on-oracle-plsql end by itlife365.com
如下是demo
create or replace procedure PRO_DEMO(I_RUN_DATE in VARCHAR DEFAULT to_char(sysdate-1,'YYYY-MM-DD') ) is
V_SQL varchar2(1000);----使用连接符拼接成一条完整SQL
V_ERROR_CODE int;
V_ERROR_MESSAGE VARCHAR2(1000);
begin
--execute immediate sql;
V_SQL := 'SELECT 1/0 FROM DUAL';
execute immediate V_SQL;
exception
when others then
rollback;
V_ERROR_CODE :=SQLCODE;
V_ERROR_MESSAGE :=SQLERRM||chr(10)||dbms_utility.format_error_backtrace();
--记录异常日志
insert into err_log(xxxx,) value(xxxx);
---null 异常时调用窗口不退出,
null;
end;
---how-continue-when-exception-on-oracle-plsql end by itlife365.com
Oracle dbms_utility 几种返回的错误网络收藏
10g版本以后提供了函数dbms_utility函数, 下面是说明
dbms_utility.format_call_stack
This Function Formats The Current Call Stack. This Can Be Used On Any Stored Procedure Or Trigger To Access The Call Stack. This Can Be Useful For Debugging.
dbms_utility.format_error_stack
This Function Formats The Current Error Stack. This Can Be Used In Exception Handlers To Look At The Full Error Stack.
dbms_utility.format_error_backtrace
This Procedure Displays The Call Stack At The Point Where An Exception Was Raised, Even If The Procedure Is Called From An Exception Handler In An Outer Scope. The Output Is Similar To The Output Of The Sqlerrm Function, But Not Subject To The Same Size Limitation.
测试的存储过程如下:---by itlife365.com oracle dbms_utility
Create Or Replace Package pkg_public_back As
Procedure p_test;
End pkg_public_back;
/
Create Or Replace Package Body pkg_public_back As
Procedure p_test3 As
a Number;
Begin
a := 0 / 0;
Exception
When Others Then
Raise;
End p_test3;
Procedure p_test2 As
Begin
p_test3;
Exception
When Others Then
Raise;
End p_test2;
Procedure p_test As
Begin
p_test2;
Exception
When Others Then
dbms_output.put_line('----format_call_stack----');
dbms_output.put_line(dbms_utility.format_call_stack);
dbms_output.put_line('-------------------------');
dbms_output.put_line('----format_error_stack----');
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line('-------------------------');
dbms_output.put_line('----format_error_backtrace----');
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line('-------------------------');
End p_test;
End pkg_public_back;
/
运行的结果如下:
---format_call_stack----
----- PL/SQL Call Stack -----
object line object
handle number name
32A86CF4 25 package body SCOTT.PKG_PUBLIC_BACK
3297A3D0 2 anonymous block
-------------------------
----format_error_stack----
ORA-01476: 除数为 0
-------------------------
----format_error_backtrace----
ORA-06512: 在 "SCOTT.PKG_PUBLIC_BACK", line 16
ORA-06512: 在 "SCOTT.PKG_PUBLIC_BACK", line 21
---end by itlife365.com oracle dbms_utility
