STATIC FUNCTION ODCIINDEXINSERT(ia SYS.ODCIINDEXINFO, rid ROWID,
                          newval NUMBER, env SYS.ODCIEnv) RETURN NUMBER IS
   stmt VARCHAR2(2000);
  BEGIN
/* Construct the SQL statement */
   stmt := 'INSERT INTO ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || 
          '_STORAGE_TAB  VALUES (:a, :b)';
/* Execute the SQL statement */
   EXECUTE IMMEDIATE stmt using newval, rid;
   RETURN ODCICONST.SUCCESS;
  END;

STATIC FUNCTION ODCIINDEXCREATE (ia SYS.ODCIINDEXINFO, parms VARCHAR2, env SYS.ODCIEnv) RETURN
 NUMBER
  IS
   stmt   VARCHAR2(2000);
  BEGIN
/* Construct the SQL statement */
   stmt := 'Create Table ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME ||
           '_STORAGE_TAB' || '(col_val, base_rowid, constraint pk PRIMARY KEY ' ||
           '(col_val, base_rowid)) ORGANIZATION INDEX AS SELECT ' ||
           ia.INDEXCOLS(1).COLNAME || ', ROWID FROM ' || 
           ia.INDEXCOLS(1).TABLESCHEMA || '.' || ia.INDEXCOLS(1).TABLENAME||

           ' where KEY1+KEY2+KEYn>0' ;

   EXECUTE IMMEDIATE stmt;
   RETURN ODCICONST.SUCCESS;
  END;

 STATIC FUNCTION ODCIINDEXINSERT(ia SYS.ODCIINDEXINFO, rid ROWID,newval NUMBER, env SYS.ODCIEnv) RETURN NUMBER IS
   stmt VARCHAR2(2000);

   s1    VARCHAR2(2000);
   key   Number; 
  BEGIN
   s1:='select KEY1+KEY2+KEYn from PPP.MAIN_TABLE where RowID=''' || rid ||'''';   
   EXECUTE IMMEDIATE s1 into key;

   if key>0 then 
      stmt := 'INSERT INTO ' || ia.INDEXSCHEMA || '.' || ia.INDEXNAME || '_STORAGE_TAB  VALUES (''' || newval || ''' , ''' || rid || ''' )';
      EXECUTE IMMEDIATE stmt;
   end if;

   RETURN ODCICONST.SUCCESS;
  END;

create or replace type NUMBER2 authid current_user as object
(
  MainVal Number,
  KeysVal Number
)

Create or replace type CARD_DELAY authid current_user as object
(
  CurNum      Number,
  HowMany     Number,
  Lower_Bound Number,
  Upper_Bound Number,
  Static function ODCIGETINTERFACES(ifClist Out SYS.ODCIObjectList)                                                Return Number,
  Static function ODCIINDEXCREATE  (iA SYS.ODCIIndexInfo,Parms Varchar2,Env SYS.ODCIEnv)                           Return Number,
  Static function ODCIINDEXDROP    (iA SYS.ODCIIndexInfo,Env SYS.ODCIEnv)                                          Return Number,
  Static function ODCIINDEXTRUNCATE(iA SYS.ODCIIndexInfo,Env SYS.ODCIEnv)                                          Return Number,
  Static function ODCIINDEXINSERT  (iA SYS.ODCIIndexInfo,Rid Rowid,Newval Number2,Env SYS.ODCIEnv)                 Return Number,
  Static function ODCIINDEXDELETE  (iA SYS.ODCIIndexInfo,Rid Rowid,Oldval Number2,Env SYS.ODCIEnv)                 Return Number,
  Static function ODCIINDEXUPDATE  (iA SYS.ODCIIndexInfo,Rid Rowid,Oldval Number2,Newval Number2,Env SYS.ODCIEnv)  Return Number,
  Static function ODCIINDEXSTART   (Sctx In Out Card_Delay,iA SYS.ODCIIndexInfo,Op SYS.ODCIPredInfo,Qi SYS.ODCIQueryInfo,Strt Number,Stop Number,Lower_Pos Number,Upper_Pos Number,Env SYS.ODCIEnv) Return Number,
  Member function ODCIINDEXFETCH   (Self In Out Card_Delay,nRows Number,Rids Out SYS.ODCIRidList,Env SYS.ODCIEnv) Return Number,
  Member function ODCIINDEXCLOSE   (Env SYS.ODCIEnv) Return Number
);

  Static function ODCIINDEXCREATE(iA SYS.ODCIIndexInfo,Parms Varchar2,Env SYS.ODCIEnv) Return Number
  is
    Stmt Varchar2(2000):='Create Table   '||iA.INDEXSCHEMA||'.'||iA.INDEXNAME||'_STORAGE_TAB(COL_VAL,BASE_ROWID,constraint PK primary key(COL_VAL,BASE_ROWID)) organization index as select '/*||iA.INDEXCOLS(1).COLNAME*/||'SALARY,ROWID from '||iA.INDEXCOLS(1).TABLESCHEMA||'.'||iA.INDEXCOLS(1).TABLENAME;
  begin
    --dbms_output.put_line(iA.INDEXCOLS(???).COLNAME);
    Execute Immediate Stmt;
    Return  ODCIConst.Success;
  end;

  Static function ODCIINDEXINSERT(iA SYS.ODCIIndexInfo,Rid Rowid,Newval Number2,Env SYS.ODCIEnv) Return Number
  is
    Stmt Varchar2(2000):='Insert into    '||iA.INDEXSCHEMA||'.'||iA.INDEXNAME||'_STORAGE_TAB values('''||Newval.MainVal||''','''||Rid||''')';
  begin
    if NewVal.KeysVal>0 then 
      Execute Immediate Stmt; 
    end if;
    Return  ODCIConst.Success;
  end;

  create index EMPLOYEES_DELAY on EMPLOYEES(Number2(SALARY,KEY1+KEY2)) indextype is CARD_DELAY_INDEXTYPE;

create type name_type
(
   first_name    char(40),
   last_name     char(40)
);
 
create type full_address_type
(
   street_address char(40),
   city               char(40),
   state             char(2),
   zip_code      number(9)
);
 
CREATE TABLE customer
(
     cust_name                  name_type,
     cust_address              full_address_type,
);

create index EMPLOYEES_DELAY on EMPLOYEES(SALARY,KEY1+KEY2) indextype is CARD_DELAY_INDEXTYPE;

create type MyIdxObj
(
   Exe   char(50),
   Key    integer  
);

create index EMPLOYEES_DELAY on EMPLOYEES(MyIdxObj(SALARY,KEY1+KEY2)) indextype is CARD_DELAY_INDEXTYPE;

Правим на Newval ANYDATA

Static function ODCIINDEXINSERT(iA SYS.ODCIIndexInfo,Rid Rowid,Newval ANYDATA,Env SYS.ODCIEnv) Return Number
is
  v_my_objects_tab  my_anydata_table;
  v_dummy NUMBER;
  v_result NUMBER := 0;
  BEGIN
    v_dummy := NewVal.GetCollection(v_my_objects_tab);
    dbms_output.put_line(NewVal.GetTypeName);                            -- ВЕРНУ MyIdxObj
    for v_i in v_my_objects_tab.FIRST..v_my_objects_tab.LAST loop  
      v_result := v_result+v_my_objects_tab(v_i).key;   -- верну значение ключа и имя ключа
    end loop;
    Return  ODCIConst.Success;
  end;

drop index     test_INDEX;
drop table     test_table;
drop indextype test_index_type;
drop operator  test_eq;
drop function  test_eq_fun;
drop type      index_methods;
drop type      Number2List;
drop type      number2;

create or replace type Number2 authid current_user as object
(
  Keys Number(18,2)
);
CREATE OR REPLACE TYPE Number2List AS VARRAY(32767) of Number2;
CREATE OR REPLACE TYPE index_methods AS OBJECT
(
  step number,
  STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexDrop(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexExchangePartition(ia SYS.ODCIIndexInfo, ia1 SYS.ODCIIndexInfo, env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexUpdPartMetadata(ia sys.ODCIIndexInfo, palist sys.ODCIPartInfoList, env sys.ODCIEnv) RETURN NUMBER,

  STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in Sys.ODCINumberList, env in SYS.ODCIEnv) RETURN NUMBER,
  static function OdciIndexInsert(iA SYS.ODCIINDEXINFO, Rid varchar2, Newval number,                                 Env SYS.ODCIEnv) Return Number,
  STATIC FUNCTION ODCIIndexDelete(ia SYS.ODCIIndexInfo, rid VARCHAR2, oldval number,                                 env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexUpdate(ia SYS.ODCIIndexInfo, rid VARCHAR2, oldval number, newval number,                 env SYS.ODCIEnv) RETURN NUMBER,

  STATIC FUNCTION ODCIIndexStart(sctx IN OUT index_methods, ia SYS.ODCIIndexInfo,op SYS.ODCIPredInfo, qi sys.ODCIQueryInfo, strt number, stop number, cmpval number, env SYS.ODCIEnv) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexFetch(self IN OUT index_methods, nrows NUMBER, rids OUT SYS.ODCIridlist, env SYS.ODCIEnv) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexClose(self IN index_methods, env SYS.ODCIEnv) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY index_methods IS

STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList) RETURN NUMBER IS
BEGIN
  ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
  dbms_output.put_line('Get');
  RETURN ODCIConst.Success;
END ODCIGetInterfaces;

STATIC FUNCTION ODCIIndexCreate (ia sys.ODCIIndexInfo, parms VARCHAR2,  env sys.ODCIEnv) RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Create');
  RETURN ODCIConst.Success;
END ODCIIndexCreate;

STATIC FUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv) RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Drop');
  RETURN ODCIConst.Success;
END ODCIIndexDrop;

STATIC FUNCTION ODCIIndexAlter (
  ia sys.ODCIIndexInfo,
  parms IN OUT VARCHAR2,
  altopt NUMBER,
  env sys.ODCIEnv)
RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Alter');
  RETURN ODCIConst.Success;
END ODCIIndexAlter;

STATIC FUNCTION ODCIIndexUpdPartMetadata(
  ia sys.ODCIIndexInfo,
  palist sys.ODCIPartInfoList,
  env sys.ODCIEnv)
RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Metadata');
  RETURN ODCIConst.Success;
END ODCIIndexUpdPartMetadata;

STATIC FUNCTION ODCIIndexExchangePartition(
  ia sys.ODCIIndexInfo,
  ia1 sys.ODCIIndexInfo,
  env sys.ODCIEnv)
RETURN NUMBER IS 
BEGIN
  dbms_output.put_line('Partition');
  RETURN ODCIConst.Success;
END ODCIIndexExchangePartition;

static function OdciIndexInsert(iA SYS.ODCIINDEXINFO,Rid varchar2,Newval number,               Env SYS.ODCIEnv) Return Number
is 
BEGIN
  dbms_output.put_line('Insert row');
  RETURN ODCIConst.Success;
END OdciIndexInsert;
                                                                                              
STATIC FUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo,ridlist sys.ODCIRidList,newvallist sys.ODCINumberList,env sys.ODCIEnv) RETURN NUMBER 
IS
  I Integer;
  C Integer; 
BEGIN 
  C:=ridList.Count;
  dbms_output.put_line('Insert array='||C);
  return ODCIConst.Success;
END;

STATIC FUNCTION ODCIIndexDelete(
   ia SYS.ODCIIndexInfo,
   rid VARCHAR2,
   oldval number,
   env SYS.ODCIEnv)
RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Delete');
  return ODCIConst.Success;
END;

STATIC FUNCTION ODCIIndexUpdate(
   ia SYS.ODCIIndexInfo,
   rid VARCHAR2,
   oldval number,
   newval number,
   env SYS.ODCIEnv)
RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Update');
  return ODCIConst.Success;
END;

STATIC FUNCTION ODCIIndexStart(
  sctx IN OUT index_methods,
  ia SYS.ODCIIndexInfo,
  op SYS.ODCIPredInfo,
  qi sys.ODCIQueryInfo,
  strt number,
  stop number,
  cmpval  number,
  env SYS.ODCIEnv)
RETURN NUMBER AS 
BEGIN
  sctx := index_methods(1);
  dbms_output.put_line('Start');
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIIndexFetch(
  self IN OUT index_methods,
  nrows NUMBER,
  rids OUT SYS.ODCIridlist,
  env SYS.ODCIEnv)
RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Fetch');
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIIndexClose(self IN index_methods, env SYS.ODCIEnv) RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Close');
  return ODCIConst.Success;
END;

end;
/
CREATE OR REPLACE FUNCTION test_eq_fun(a number, b number) RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Execute');
  IF a = b then RETURN 1; ELSE RETURN 0; END IF;
END;
/
CREATE or replace OPERATOR test_eq          BINDING (number, number) RETURN NUMBER 
--with index context, scan context index_methods 
USING test_eq_fun;

/

CREATE INDEXTYPE test_index_type FOR test_eq(number, number) USING index_methods WITH ARRAY DML;
--WITH LOCAL RANGE PARTITION
--WITH SYSTEM MANAGED STORAGE TABLES;
--COMMIT;
CREATE TABLE test_table (id NUMBER(18,2),nm varchar2(10));
CREATE INDEX test_index ON test_table(id) INDEXTYPE IS test_index_type;
insert into test_table values(0,'One');
insert into test_table select A.* from (select LEVEL F1,'Array' F2 from DUAL connect by LEVEL<301) A;
commit;
select * 
  from test_table
 where test_eq(1,1)=1;

array_DML_clause 
Use this clause to let the indextype support the array interface for the ODCIIndexInsert method.
type and varray_type 
If the data type of the column to be indexed is a user-defined object type, then you must specify this clause to identify the varray varray_type that Oracle should use to hold column values of type. If the indextype supports a list of types, then you can specify a corresponding list of varray types. If you omit schema for either type or varray_type, then Oracle assumes the type is in your own schema.
If the data type of the column to be indexed is a built-in system type, then any varray type specified for the indextype takes precedence over the ODCI types defined by the system.

drop index     test_INDEX;
drop table     test_table;
drop indextype test_index_type;
drop operator  test_eq;
drop function  test_eq_fun;
drop type      index_methods;
drop type      Number2List;
drop type      number2;

create or replace type Number2 authid current_user as object
(
  Keys Number(18,2)
);
CREATE OR REPLACE TYPE Number2List AS VARRAY(32767) of Number2;
CREATE OR REPLACE TYPE index_methods AS OBJECT
(
  step number,
  STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexDrop(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexExchangePartition(ia SYS.ODCIIndexInfo, ia1 SYS.ODCIIndexInfo, env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexUpdPartMetadata(ia sys.ODCIIndexInfo, palist sys.ODCIPartInfoList, env sys.ODCIEnv) RETURN NUMBER,

  STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in Number2List, Env in SYS.ODCIEnv) RETURN NUMBER,
  static function OdciIndexInsert(iA SYS.ODCIINDEXINFO, Rid varchar2, Newval Number2,                             Env SYS.ODCIEnv) Return Number,
  STATIC FUNCTION ODCIIndexDelete(ia SYS.ODCIIndexInfo, rid VARCHAR2, Oldval Number2,                             Env SYS.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexUpdate(ia SYS.ODCIIndexInfo, rid VARCHAR2, Oldval Number2, Newval Number2,             Env SYS.ODCIEnv) RETURN NUMBER,

  STATIC FUNCTION ODCIIndexStart(sctx IN OUT index_methods, ia SYS.ODCIIndexInfo,op SYS.ODCIPredInfo, qi sys.ODCIQueryInfo, strt number, stop number, cmpval number, env SYS.ODCIEnv) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexFetch(self IN OUT index_methods, nrows NUMBER, rids OUT SYS.ODCIridlist, env SYS.ODCIEnv) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexClose(self IN index_methods, env SYS.ODCIEnv) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY index_methods IS

STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList) RETURN NUMBER IS
BEGIN
  ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
  dbms_output.put_line('Get');
  RETURN ODCIConst.Success;
END ODCIGetInterfaces;

STATIC FUNCTION ODCIIndexCreate (ia sys.ODCIIndexInfo, parms VARCHAR2,  env sys.ODCIEnv) RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Create');
  RETURN ODCIConst.Success;
END ODCIIndexCreate;

STATIC FUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv) RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Drop');
  RETURN ODCIConst.Success;
END ODCIIndexDrop;

STATIC FUNCTION ODCIIndexAlter (
  ia sys.ODCIIndexInfo,
  parms IN OUT VARCHAR2,
  altopt NUMBER,
  env sys.ODCIEnv)
RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Alter');
  RETURN ODCIConst.Success;
END ODCIIndexAlter;

STATIC FUNCTION ODCIIndexUpdPartMetadata(
  ia sys.ODCIIndexInfo,
  palist sys.ODCIPartInfoList,
  env sys.ODCIEnv)
RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Metadata');
  RETURN ODCIConst.Success;
END ODCIIndexUpdPartMetadata;

STATIC FUNCTION ODCIIndexExchangePartition(
  ia sys.ODCIIndexInfo,
  ia1 sys.ODCIIndexInfo,
  env sys.ODCIEnv)
RETURN NUMBER IS 
BEGIN
  dbms_output.put_line('Partition');
  RETURN ODCIConst.Success;
END ODCIIndexExchangePartition;

static function OdciIndexInsert(iA SYS.ODCIINDEXINFO,Rid varchar2,Newval number2,               Env SYS.ODCIEnv) Return Number
is 
BEGIN
  dbms_output.put_line('Insert row');
  RETURN ODCIConst.Success;
END OdciIndexInsert;
                                                                                              
STATIC FUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo,ridlist sys.ODCIRidList,newvallist Number2List,env sys.ODCIEnv) RETURN NUMBER 
IS
  I Integer;
  C Integer; 
BEGIN 
  C:=ridList.Count;
  dbms_output.put_line('Insert array='||C);
  return ODCIConst.Success;
END;

STATIC FUNCTION ODCIIndexDelete(
   ia SYS.ODCIIndexInfo,
   rid VARCHAR2,
   oldval number2,
   env SYS.ODCIEnv)
RETURN NUMBER IS
BEGIN
  dbms_output.put_line('Delete');
  return ODCIConst.Success;
END;

STATIC FUNCTION ODCIIndexUpdate(
   ia SYS.ODCIIndexInfo,
   rid VARCHAR2,
   oldval number2,
   newval number2,
   env SYS.ODCIEnv)
RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Update');
  return ODCIConst.Success;
END;

STATIC FUNCTION ODCIIndexStart(
  sctx IN OUT index_methods,
  ia SYS.ODCIIndexInfo,
  op SYS.ODCIPredInfo,
  qi sys.ODCIQueryInfo,
  strt number,
  stop number,
  cmpval  number,
  env SYS.ODCIEnv)
RETURN NUMBER AS 
BEGIN
  sctx := index_methods(1);
  dbms_output.put_line('Start');
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIIndexFetch(
  self IN OUT index_methods,
  nrows NUMBER,
  rids OUT SYS.ODCIridlist,
  env SYS.ODCIEnv)
RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Fetch');
  return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIIndexClose(self IN index_methods, env SYS.ODCIEnv) RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Close');
  return ODCIConst.Success;
END;

end;
/
CREATE OR REPLACE FUNCTION test_eq_fun(a number, b number) RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Execute');
  IF a = b then RETURN 1; ELSE RETURN 0; END IF;
END;
/
CREATE or replace OPERATOR test_eq          BINDING (number, number) RETURN NUMBER 
--with index context, scan context index_methods 
USING test_eq_fun;

/

CREATE INDEXTYPE test_index_type FOR test_eq(number, number) USING index_methods WITH ARRAY DML(Number2,Number2List);
--WITH LOCAL RANGE PARTITION
--WITH SYSTEM MANAGED STORAGE TABLES;
--COMMIT;
CREATE TABLE test_table (id NUMBER(18,2),nm varchar2(10));
CREATE INDEX test_index ON test_table(Number2(id)) INDEXTYPE IS test_index_type;
insert into test_table values(0,'One');
insert into test_table select A.* from (select Level F1,'Array' F2 from DUAL connect by LEVEL<301) A;
commit;
select * 
  from test_table
 where test_eq(1,1)=1;