有网友问一个存储过程问题,加上下划线("_")就会报错,测试了一下确实如此,测试环境trace一下,找出原因
SQL> DECLARE
2 v_num number;
3 v_type varchar2(30);
4 BEGIN
5 v_type:='USER_';
6 dbms_output.put_line(v_type);
7 execute immediate 'select length('||v_type||') from dual' into v_num;
8 dbms_output.put_line(v_num);
9 END;
10 /
USER_
DECLARE
*
ERROR at line 1:
ORA-00904: "USER_": invalid identifier
ORA-06512: at line 7
SQL>
SQL> alter session set sql_trace=TRUE;
Session altered.
SQL> DECLARE
2 v_num number;
3 v_type varchar2(30);
4 BEGIN
5 v_type:='USE_';
6 dbms_output.put_line(v_type);
7 execute immediate 'select length('||v_type||') from dual' into v_num;
8 dbms_output.put_line(v_num);
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-00904: "USE_": invalid identifier
ORA-06512: at line 7
SQL> alter session set sql_trace=false;
Session altered.
查看trace文件orcl_ora_5092.trc(贴出部分内容)
PARSE ERROR #2:len=29 dep=1 uid=61 ct=3 lid=61 tim=1295570213449697 err=904
select length(USE_) from dual --可以看到具体原因在转换过程中少了点引号
EXEC #1:c=2000,e=1925,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1295570213450763
ERROR #1:err=904 tim=429491641
修改存储过程,执行成功
SQL> set serveroutput on
SQL> DECLARE
2 v_num number;
3 v_type varchar2(30);
4 BEGIN
5 v_type:='USER_';
6 dbms_output.put_line(v_type);
7 execute immediate 'select length('''||v_type||''') from dual' into v_num;
8 dbms_output.put_line(v_num);
9 END;
10 /
USER_
5
PL/SQL procedure successfully completed.
SQL>
总结:
在日常操作中注意oracle内部转换问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25148509/viewspace-715091/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25148509/viewspace-715091/