对自定义函数建立索引时抛异常ORA-30553
--ORA-30553:
函数不能确定(
ORA-30553: The function is not deterministic
)解决方法
function integer table sql
对自定义函数建立索引时抛异常
--ORA-30553:
函数不能确定(
ORA-30553: The function is not deterministic
)解决方法
问题描述:
SQL> create or replace function f_16turnto10(
2 str_16char in varchar2
3 )return integer
4 is
5 i_number number(10);
6 i_int int;
7 begin
8 i_int := length(str_16char)+10;
9 i_number := to_number(str_16char,rpad('x',i_int,'x'));
10 return i_number;
11 end;
12 /
Function created
SQL> create table t_16turn10 as select username,'4D' as psw from dba_users;
Table created
SQL> create index i_16turn10 on t_16turn10 (f_16turnto10(psw));
create index i_16turn10 on t_16turn10 (f_16turnto10(psw))
ORA-30553:
函数不能确定
解决如下:
如果需要创建基于自定义函数的索引
,
那么我们需要指定
deterministic
参数,在函数建立的时候指定该参数问题可解决。
SQL> create or replace function f_16turnto10(
2 str_16char in varchar2
3 )return integer deterministic
4 is
5 i_number number(10);
6 i_int int;
7 begin
8 i_int := length(str_16char)+10;
9 i_number := to_number(str_16char,rpad('x',i_int,'x'));
10 return i_number;
11 end;
12 /
Function created
SQL> create table t_16turn10 as select username,'4D' as psw from dba_users;
Table created
SQL> create index i_16turn10 on t_16turn10 (f_16turnto10(psw));
Index created