ORA-30553

　　建立函數索引時,如果函數是自定義函數,則會報ORA-30553錯誤，在自定義函數中加上DETERMINISTIC可以解決這個問題！

[@more@]

SCOTT@HUIYI>select empno,ename,sal from emp;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 SMITH                       800
      7499 ALLEN                      1600
      7521 WARD                       1250
      7566 JONES                      2975
      7654 MARTIN                     1250
      7698 BLAKE                      2850
      7782 CLARK                      2450
      7788 SCOTT                      3000
      7839 KING                       5000
      7844 TURNER                     1500
      7876 ADAMS                      1100
      7900 JAMES                       950
      7902 FORD                       3000
      7934 MILLER                     1300

14 rows selected.

SCOTT@HUIYI>create or replace function add_sal(p_value number) return number is
  2    l_result number;
  3  begin
  4    l_result := p_value + 1;
  5    return l_result;
  6  end;
  7  /

Function created.

SCOTT@HUIYI>create index add_sal_idx on emp(add_sal(sal));
create index add_sal_idx on emp(add_sal(sal))
                                *
ERROR at line 1:




SCOTT@HUIYI>create or replace function add_sal(p_value number) return number DETERMINISTIC is
  2    l_result number;
  3  begin
  4    l_result := p_value + 1;
  5    return l_result;
  6  end;
  7  /

Function created.

SCOTT@HUIYI>create index emp_sal_fun_idx on emp(add_sal(sal));

Index created.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8799875/viewspace-904528/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8799875/viewspace-904528/