网上查找的一个解决方式
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
oracle 存储过程中如何并行跑
根据网上的说法，使用job无法解决，如下是过程。
下面使用job方法
create or replace procedure PRO_DEMO is
V1 VARCHAR2(100);
V_JOBNO1 VARCHAR2(100);
V_JOBNO2 VARCHAR2(100);
V_JOBNO3 VARCHAR2(100);
V_JOBNO VARCHAR2(100);
V_SUBMIT_PRO_NAME VARCHAR2(100);
begin
// 根据顺序串行跑
dbms_job.submit(V_JOBNO,'PROC_ODS_ZS_JKS(''2017-06-21'');PROC_ODS_ZS_SRTHS(''2017-06-21'');PROC_ODS_DJ_NSRXX(''2017-06-21'');',sysdate,'null');
DBMS_JOB.run(V_JOBNO);
end PRO_DEMO;
SQL> exec pro_demo;
PROC_ODS_ZS_JKS=====1 23-6月 -17
PROC_ODS_ZS_JKS=====2 23-6月 -17
PROC_ODS_ZS_JKS=====3 23-6月 -17
PROC_ODS_ZS_JKS=====4 23-6月 -17
PROC_ODS_ZS_JKS=====5 23-6月 -17
PROC_ODS_ZS_JKS=====6 23-6月 -17
PROC_ODS_ZS_JKS=====7 23-6月 -17
PROC_ODS_ZS_JKS=====8 23-6月 -17
PROC_ODS_ZS_JKS=====9 23-6月 -17
PROC_ODS_ZS_JKS=====10 23-6月 -17
PROC_ODS_ZS_JKS=====11 23-6月 -17
PROC_ODS_ZS_JKS=====12 23-6月 -17
PROC_ODS_ZS_JKS=====13 23-6月 -17
PROC_ODS_ZS_JKS=====14 23-6月 -17
PROC_ODS_ZS_JKS=====15 23-6月 -17
PROC_ODS_ZS_SRTHS=====1 23-6月 -17
PROC_ODS_ZS_SRTHS=====2 23-6月 -17
PROC_ODS_ZS_SRTHS=====3 23-6月 -17
PROC_ODS_ZS_SRTHS=====4 23-6月 -17
PROC_ODS_ZS_SRTHS=====5 23-6月 -17
PROC_ODS_ZS_SRTHS=====6 23-6月 -17
PROC_ODS_ZS_SRTHS=====7 23-6月 -17
PROC_ODS_ZS_SRTHS=====8 23-6月 -17
PROC_ODS_ZS_SRTHS=====9 23-6月 -17
PROC_ODS_ZS_SRTHS=====10 23-6月 -17
PROC_ODS_ZS_SRTHS=====11 23-6月 -17
PROC_ODS_ZS_SRTHS=====12 23-6月 -17
PROC_ODS_ZS_SRTHS=====13 23-6月 -17
PROC_ODS_ZS_SRTHS=====14 23-6月 -17
PROC_ODS_ZS_SRTHS=====15 23-6月 -17
PROC_ODS_DJ_NSRXX=====1 23-6月 -17
PROC_ODS_DJ_NSRXX=====2 23-6月 -17
PROC_ODS_DJ_NSRXX=====3 23-6月 -17
PROC_ODS_DJ_NSRXX=====4 23-6月 -17
PROC_ODS_DJ_NSRXX=====5 23-6月 -17
PROC_ODS_DJ_NSRXX=====6 23-6月 -17
PROC_ODS_DJ_NSRXX=====7 23-6月 -17
PROC_ODS_DJ_NSRXX=====8 23-6月 -17
PROC_ODS_DJ_NSRXX=====9 23-6月 -17
PROC_ODS_DJ_NSRXX=====10 23-6月 -17
PROC_ODS_DJ_NSRXX=====11 23-6月 -17
PROC_ODS_DJ_NSRXX=====12 23-6月 -17
PROC_ODS_DJ_NSRXX=====13 23-6月 -17
PROC_ODS_DJ_NSRXX=====14 23-6月 -17
PROC_ODS_DJ_NSRXX=====15 23-6月 -17
PROC_ODS_DJ_NSRXX=====16 23-6月 -17
PROC_ODS_DJ_NSRXX=====17 23-6月 -17
PROC_ODS_DJ_NSRXX=====18 23-6月 -17
PROC_ODS_DJ_NSRXX=====19 23-6月 -17
PROC_ODS_DJ_NSRXX=====20 23-6月 -17
PROC_ODS_DJ_NSRXX=====21 23-6月 -17
PROC_ODS_DJ_NSRXX=====22 23-6月 -17
PROC_ODS_DJ_NSRXX=====23 23-6月 -17
PROC_ODS_DJ_NSRXX=====24 23-6月 -17
PROC_ODS_DJ_NSRXX=====25 23-6月 -17
PROC_ODS_DJ_NSRXX=====26 23-6月 -17
PROC_ODS_DJ_NSRXX=====27 23-6月 -17
PL/SQL procedure successfully completed
SQL>
create or replace procedure PRO_DEMO is
V1 VARCHAR2(100);
V_JOBNO1 VARCHAR2(100);
V_JOBNO2 VARCHAR2(100);
V_JOBNO3 VARCHAR2(100);
V_JOBNO VARCHAR2(100);
V_SUBMIT_PRO_NAME VARCHAR2(100);
begin
dbms_job.submit(V_JOBNO1,'PROC_ODS_ZS_JKS(''2017-06-21'');',sysdate,'null');-- V_PRO_NAME为存储过程名称，后面要带着分号 (;) interval =null --run one only
dbms_job.submit(V_JOBNO2,'PROC_ODS_DJ_NSRXX(''2017-06-21'');',sysdate,'null');
dbms_job.submit(V_JOBNO3,'PROC_ODS_ZS_SRTHS(''2017-06-21'');',sysdate,'null');
--dbms_job.submit(V_JOBNO,'PROC_ODS_ZS_JKS(''2017-06-21'');PROC_ODS_ZS_SRTHS(''2017-06-21'');PROC_ODS_DJ_NSRXX(''2017-06-21'');',sysdate,'null');
-- DBMS_JOB.run(V_JOBNO);
DBMS_JOB.run(V_JOBNO1);
DBMS_JOB.run(V_JOBNO2);
DBMS_JOB.run(V_JOBNO3);
end PRO_DEMO;
SQL> exec pro_demo;//多次跑的效果都是不一样的，有时会报错。
PROC_ODS_ZS_JKS=====1 23-6月 -17
PROC_ODS_ZS_JKS=====2 23-6月 -17
PROC_ODS_ZS_JKS=====3 23-6月 -17
PROC_ODS_ZS_JKS=====4 23-6月 -17
PROC_ODS_ZS_JKS=====5 23-6月 -17
PROC_ODS_ZS_JKS=====6 23-6月 -17
PROC_ODS_ZS_JKS=====7 23-6月 -17
PROC_ODS_ZS_JKS=====8 23-6月 -17
PROC_ODS_ZS_JKS=====9 23-6月 -17
PROC_ODS_ZS_JKS=====10 23-6月 -17
PROC_ODS_ZS_JKS=====11 23-6月 -17
PROC_ODS_ZS_JKS=====12 23-6月 -17
PROC_ODS_ZS_JKS=====13 23-6月 -17
PROC_ODS_ZS_JKS=====14 23-6月 -17
PROC_ODS_ZS_JKS=====15 23-6月 -17
begin pro_demo; end;
ORA-23421: 作业编号138在作业队列中不是一个作业
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: 在 "SYS.DBMS_IJOB", line 781
ORA-06512: 在 "SYS.DBMS_JOB", line 267
ORA-06512: 在 "BXTGL.PRO_DEMO", line 22
ORA-06512: 在 line 1
SQL>
how-oracle-procedure-para-run
----
