Why am I getting ORA-00904: invalid identifier for deterministic function index?

Question

create or replace function idxF(status IN char) return char deterministic is
retVal CHAR(1);
begin
  dbms_output.put_line('P');
  retVal:=CASE status
  when 'P' then 'P'
  when 'H' then 'H'
  else null
  end  ;
  return retVal;
end idxF;
  create index setIndexOnStatus on ORDER(idXF(STATUS));

  SQL Error: ORA-00904: "IDXF": invalid identifier

STATUS is a CHAR(1) column in ORDER table.

Why is the compiler saying that IDXF is an invalid identifier?

Is this your real function? `dbms_output.put_line('P');` will create problems sooner or later. — Wernfried Domscheit, May 23 '18 at 09:14
Why not simply `CREATE INDEX setIndexOnStatus ON "ORDER" (CASE status WHEN 'P' THEN 'P' WHEN 'H' THEN 'H' ELSE NULL END);` or `CREATE INDEX setIndexOnStatus ON "ORDER" (CASE WHEN status IN ('P', 'H') THEN status ELSE NULL END);`? — Wernfried Domscheit, May 23 '18 at 09:17

default locale · Answer 1 · 2018-05-23T09:21:33.617

ORDER is a reserved word in Oracle (part of ORDER BY clause) so you shouldn't use it as a name for your table.

Technically, you can escape a reserved word with double quotes:

create index setIndexOnStatus on "ORDER"(idXF(STATUS));

Probably, this table was created like this in a first place.

This approach is extremely problematic because you'll need to escape every query to this table and it's not always possible, especially with auto-generated queries.

So, don't go this way, rename the table instead.

P.S. I couldn't reproduce ORA-00904 on this query. Invalid table name leads to ORA-00903: invalid table name. Escaped query works fine for me. If you still have ORA-00904 error then you'll need to build a reproducible example to demonstrate your problem (i.e. include CREATE TABLE statement and specify the Oracle version).

Why am I getting ORA-00904: invalid identifier for deterministic function index?

1 Answers1