show parameter max_string_size

NAME            TYPE   VALUE    
--------------- ------ -------- 
max_string_size string EXTENDED 



CREATE OR REPLACE FUNCTION SSF_GET_FORMATTED_NAME(
    p_first_nm       IN VARCHAR2,
    p_last_nm        IN VARCHAR2 
) RETURN VARCHAR2
    DETERMINISTIC
IS
BEGIN
    RETURN UPPER(regexp_replace( p_first_nm, '[^[:alnum:]]') || '~' || regexp_replace(p_last_nm, '[^[:alnum:]]'));
END;
/


CREATE TABLE TEST_VIRTUAL_COL (
    FIRST_NM VARCHAR2(25 CHAR),
    LAST_NM  VARCHAR2(30 CHAR) 
);

CREATE INDEX FORMATTED_NAME_INDEX ON TEST_VIRTUAL_COL(SSF_GET_FORMATTED_NAME(FIRST_NM, LAST_NM));


Error starting at line : 11 in command -
CREATE INDEX FORMATTED_NAME_INDEX ON TEST_VIRTUAL_COL(FORMATTED_NAME)
Error report -
ORA-01450: maximum key length (6398) exceeded
01450. 00000 -  "maximum key length (%s) exceeded"
*Cause:    
*Action:

The indexed values must be able to fit in a block. As you've enabled extended data types, the function could return strings up to 32,767 bytes long. 4x more than the default blocksize (8Kb)!

You can get around this by casting the function output. I'd do this in a virtual column, then indexing that:

create table test_virtual_col (
  first_nm       varchar2(25 char),
  last_nm        varchar2(30 char),
  formatted_name varchar2(100 char) as (
    cast ( 
      ssf_get_formatted_name(first_nm, last_nm) 
        as varchar2(100 char) 
    )
  )
);

create index formatted_name_index 
  on test_virtual_col (
    formatted_name
  );

Queries against the virtual column can then use this index:

insert into test_virtual_col 
  values ( 'Chris', 'Saxon', default );
  
set serveroutput off
select * from test_virtual_col
where  formatted_name = 'CHRIS~SAXON';
select * 
from   dbms_xplan.display_cursor( format => 'BASIC' );

--------------------------------------------------------------------
| Id  | Operation                           | Name                 |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_VIRTUAL_COL     |
|   2 |   INDEX RANGE SCAN                  | FORMATTED_NAME_INDEX |
--------------------------------------------------------------------

This is easier than calling the function and avoids mismatch issues. You can use cast ( fn as ... ) in the index itself, but then all queries must have an identical cast expression to use the index. If you write one with a different length, the optimizer is unable to use it!

For other ways to solve the ORA-01450 error, see
https://blogs.oracle.com/sql/post/how-to-fix-ora-01450-maximum-key-length-6398-exceeded-errors