Have you tried changing the function parameter type from varchar2 to
Orders.Code%type to see if providing an explicit length to the paramer makes a
difference?
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
Sent: Wednesday, June 22, 2016 8:13:50 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Cannot create an index based on a function
Hello,
we are trying to apply a unique index for NULL values using an index like this:
create unique index tk_test on orders (code, coalesce(p_versionid,'-1'));
The above index is doing exactly what we want (allowing only a single NULL
value for the same CODE column), except that SAP Hybris refuses to work with a
database that contains such an index.
An ugly workaround the stupid Hybris restriction is to wrap the expression in
the index into a function:
CREATE OR REPLACE function coalesce_wrapper(param in varchar2)
return varchar2 deterministic
is
begin
return coalesce(param, '-1');
end;
/
But for some reason when creating an index using:
create unique index tk_test on orders (code, coalesce_wrapper(p_versionid));
This results in:
ORA-01450: maximum key length (6398) exceeded
However: the column p_versionid is defined as VARCHAR2(255 Byte) and so is the
CODE column - and as I said: the equivalent index using coalesce() works just
fine.
It seems that Oracle uses the function's signature to decide whether or not the
value can be indexed rather than the actual value returned by the function.
The statement:
select max(length(p_versionid)), max(length(coalesce_wrapper(p_versionid)))
from orders;
returns 30 for both values. So my understanding would be that this function
should be usable in an index, but apparently it isn't.
This is an Oracle 12.1.0.2 EE running on Linux.
Any ideas how I can convince Oracle that the index expression is not longer
then 30 characters?
Thanks
Thomas
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
