Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Function Based Index
Hi all,
I have a strange behaviour with function based indexes in Oracle 8i on NT 4.0. I want to use a function-based index on a user function. It works nicely with internal functions. Here is what I get:
SQL>
SQL> drop table test_tab;
Table dropped.
SQL> create table test_tab (tststr varchar2 (30));
Table created.
SQL>
SQL> create or replace function test_func (
2 s in test_tab.tststr%type
3 ) return test_tab.tststr%type deterministic is
4 begin
5 return upper (trim
(s));
6 end test_func;
7 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> describe test_func
FUNCTION test_func RETURNS VARCHAR2(30)
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------
--------
S
VARCHAR2(30)
IN
SQL>
SQL> -- We know that test_tab_test_func_1a is not working according
to the docs, but we try anyhow. But we get ORA-01450.
SQL>
SQL> create index test_tab_test_func_1a
2 on test_tab (test_func (tststr));
on test_tab (test_func (tststr))
*
ERROR at line 2:
ORA-01450: maximum key length (3218) exceeded
SQL>
SQL> -- According to the Oracle8i examples test_tab_test_func_1b should
work. But we get ORA-04020.
SQL>
SQL> create index test_tab_test_func_1b
2 on test_tab (substr (test_func (tststr), 1, 30));
on test_tab (substr (test_func (tststr), 1, 30))
*
ERROR at line 2:
ORA-04020: deadlock detected while trying to lock object
TEST_MATCH.TEST_TAB
SQL>
SQL> -- Surprise, surprise - test_tab_test_func_2 works although the
function is merely the same.
SQL>
SQL> create index test_tab_test_func_2
2 on test_tab (upper (trim (tststr)));
Index created.
SQL>
SQL> spool off
Can somebody please shed some light on this? I appreciate any help.
Martin Received on Sun Nov 14 1999 - 17:28:42 CST
![]() |
![]() |