--https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_examples001.htm#SQLRF55547
drop index     Sv_RccDelayIndex;
drop indextype Sv_RccDelayType;
drop operator  Sv_RccDelayValue;
drop function  Sv_RccDelayExe;
drop type      Sv_RccDelay;
drop type      Sv_RccKey;
drop table     Sv_Rcc;

create table Sv_Rcc(DEAL Varchar2(24),DREP Date,AMOUNT Number,KEY1 Number,KEY2 Number);
insert into  Sv_Rcc values('Tucker'    ,To_Date('02-09-2021','DD-MM-YYYY'),1001,1,2);
insert into  Sv_Rcc values('King'      ,To_Date('04-09-2021','DD-MM-YYYY'),1002,0,0);
insert into  Sv_Rcc values('Baer'      ,To_Date('07-09-2021','DD-MM-YYYY'),1003,1,2);
insert into  Sv_Rcc values('Bloom'     ,To_Date('09-09-2021','DD-MM-YYYY'),1004,1,2);
insert into  Sv_Rcc values('Fox'       ,To_Date('12-09-2021','DD-MM-YYYY'),9605,1,2);
insert into  Sv_Rcc values('Bernstein' ,To_Date('14-10-2021','DD-MM-YYYY'),9506,1,2);
insert into  Sv_Rcc values('Sully'     ,To_Date('18-10-2021','DD-MM-YYYY'),9507,1,2);
insert into  Sv_Rcc values('Greene'    ,To_Date('20-10-2021','DD-MM-YYYY'),9508,0,0);
insert into  Sv_Rcc values('Hunold'    ,To_Date('22-10-2021','DD-MM-YYYY'),9009,1,2);
insert into  Sv_Rcc values('Faviet'    ,To_Date('23-10-2021','DD-MM-YYYY'),9010,1,2);
insert into  Sv_Rcc values('McEwen'    ,To_Date('25-10-2021','DD-MM-YYYY'),9011,1,2);
insert into  Sv_Rcc values('Hall'      ,To_Date('26-10-2021','DD-MM-YYYY'),9012,1,2);
insert into  Sv_Rcc values('Hutton'    ,To_Date('28-10-2021','DD-MM-YYYY'),8813,1,2);
insert into  Sv_Rcc values('Taylor'    ,To_Date('01-11-2021','DD-MM-YYYY'),8614,1,2);
insert into  Sv_Rcc values('Livingston',To_Date('02-11-2021','DD-MM-YYYY'),8415,0,0);
insert into  Sv_Rcc values('Gietz'     ,To_Date('04-11-2021','DD-MM-YYYY'),8316,1,2);
insert into  Sv_Rcc values('Chen'      ,To_Date('07-11-2021','DD-MM-YYYY'),8217,1,2);
insert into  Sv_Rcc values('Fripp'     ,To_Date('09-11-2021','DD-MM-YYYY'),8218,1,2);
insert into  Sv_Rcc values('Weiss'     ,To_Date('11-12-2021','DD-MM-YYYY'),8019,0,0);
insert into  Sv_Rcc values('Olsen'     ,To_Date('12-12-2021','DD-MM-YYYY'),8020,1,2);
insert into  Sv_Rcc values('Smith'     ,To_Date('13-12-2021','DD-MM-YYYY'),8021,1,2);
insert into  Sv_Rcc values('Kaufling'  ,To_Date('15-12-2021','DD-MM-YYYY'),7922,1,2);
commit;

create or replace type SV_RccKey authid current_user as object
(
  Deal Varchar2(24),
  Drep Date,
  Keys Number
);
/
create or replace type SV_RccDelay authid current_user as object
(
  CurNum Number,
  cCount Number,
  static function SqlText(iKind in String,iSchema in String,iTable in String) Return String,
  static function OdciGetInterFaces(ifClist out SYS.ODCIOBJECTLIST                                                  ) Return Number, 
  static function OdciIndexCreate  (iA SYS.ODCIINDEXINFO,Parms Varchar2,                             Env SYS.ODCIEnv) Return Number,
  static function OdciIndexTruncate(iA SYS.ODCIINDEXINFO,                                            Env SYS.ODCIEnv) Return Number,
  static function OdciIndexDrop    (iA SYS.ODCIINDEXINFO,                                            Env SYS.ODCIEnv) Return Number,
  static function OdciIndexInsert  (iA SYS.ODCIINDEXINFO,Rid ROWID,Newval SV_RccKey,                 Env SYS.ODCIEnv) Return Number,
  static function OdciIndexDelete  (iA SYS.ODCIINDEXINFO,Rid ROWID,Oldval SV_RccKey,                 Env SYS.ODCIEnv) Return Number,
  static function OdciIndexUpdate  (iA SYS.ODCIINDEXINFO,Rid ROWID,Oldval SV_RccKey,Newval SV_RccKey,Env SYS.ODCIEnv) Return Number,
  static function OdciIndexStart   (Sctx in out Sv_RccDelay,iA SYS.ODCIINDEXINFO,oP SYS.ODCIPREDINFO,qI SYS.ODCIQUERYINFO,Strt Number,Stop Number,Env SYS.ODCIEnv) Return Number,
  member function OdciIndexFetch   (Self in out Sv_RccDelay,nRows Number,Rids out SYS.ODCIRIDLIST,   Env SYS.ODCIEnv) Return Number,
  member function OdciIndexClose   (                                                                 Env SYS.ODCIEnv) Return Number
);
/
create or replace type body Sv_RccDelay
is
  static function SqlText(iKind in String,iSchema in String,iTable in String) Return String
  is 
    type ListText is table of varchar2(1000) index by varchar2(30);
    SqlList ListText;
  begin 
    SqlList('Merge'):='merge into :Schema.:Table_TT H
using (select :Deal DEAL,Cast(:Drep as Date) DREP,:Rid BASE_ROWID,Sign(:Keys) EDIT from DUAL) T
   on (H.DEAL=T.DEAL)
 when     MATCHED then update set   H.DREP      =Case when H.DREP>T.DREP then T.DREP                    else H.DREP       end,
                                    H.BASE_ROWID=Case when H.DREP>T.DREP then CharToRowId(T.BASE_ROWID) else H.BASE_ROWID end
                       delete where T.EDIT=0
 when not MATCHED then insert      (H.DEAL,H.DREP,H.BASE_ROWID)
                       values      (T.DEAL,T.DREP,T.BASE_ROWID)
                        where       T.EDIT=1
                         ';
    SqlList('Erase' ):='truncate table         :Schema.:Table_TT';
    SqlList('Drop'  ):='drop table             :Schema.:Table_TT';
    SqlList('Select'):='select BASE_ROWID from :Schema.:Table_TT';
    SqlList('Create'):='create table           :Schema.:Table_TT (DEAL,DREP,BASE_ROWID, constraint :Table_PK primary key(DEAL)) organization index as select DEAL,Max(DREP) DREP,Min(ROWID) keep(Dense_Rank First order by DREP) BASE_ROWID from :Base where KEY1+KEY2>0 group by DEAL';
    return Replace(Replace(SqlList(iKind),':Schema',iSchema),':Table',iTable);
  end;
  
  static function OdciGetInterFaces(ifClist out SYS.ODCIOBJECTLIST                                               ) Return Number
  is 
  begin
    dbms_output.put_line('1');
    ifClist := SYS.ODCIOBJECTLIST(SYS.ODCIOBJECT('SYS','ODCIINDEX2'));
    Return ODCIConst.Success;
  end;

  static function OdciIndexCreate  (iA SYS.ODCIINDEXINFO,Parms Varchar2,                          Env SYS.ODCIEnv) Return Number
  is
  begin
    dbms_output.put_line('2');
    execute immediate Replace(SqlText('Create',ia.INDEXSCHEMA,ia.INDEXNAME),':Base',ia.INDEXCOLS(1).TABLESCHEMA||'.'||ia.INDEXCOLS(1).TABLENAME);
    Return ODCIConst.Success;
  end;
   
  static function OdciIndexTruncate(iA SYS.ODCIINDEXINFO,                                         Env SYS.ODCIEnv) Return Number
  is
  begin
    dbms_output.put_line('3');
    execute immediate SqlText('Erase',ia.INDEXSCHEMA,ia.INDEXNAME);
    Return ODCIConst.Success;
  end;

  static function OdciIndexDrop    (iA SYS.ODCIINDEXINFO,                                         Env SYS.ODCIEnv) Return Number
  is
  begin
    dbms_output.put_line('4');
    execute immediate SqlText('Drop',ia.INDEXSCHEMA,ia.INDEXNAME);
    Return ODCIConst.Success;
  end;

  static function OdciIndexInsert  (iA SYS.ODCIINDEXINFO,Rid ROWID,Newval SV_RccKey,                 Env SYS.ODCIEnv) Return Number
  is
  begin
    dbms_output.put_line('5');
    execute immediate SqlText('Merge',ia.INDEXSCHEMA,ia.INDEXNAME) using NewVal.Deal,NewVal.Drep,RowIdToChar(Rid),NewVal.Keys;
    Return ODCIConst.Success;
  end;
  
  static function OdciIndexDelete  (iA SYS.ODCIINDEXINFO,Rid ROWID,Oldval SV_RccKey,                 Env SYS.ODCIEnv) Return Number
  is
  begin
    dbms_output.put_line('6');
    execute immediate SqlText('Merge',ia.INDEXSCHEMA,ia.INDEXNAME) using OldVal.Deal,OldVal.Drep,RowIdToChar(Rid),0;
    Return ODCIConst.Success;
  end;

  static function OdciIndexUpdate  (iA SYS.ODCIINDEXINFO,Rid ROWID,Oldval SV_RccKey,Newval SV_RccKey,   Env SYS.ODCIEnv) Return Number
  is
  begin
    dbms_output.put_line('7');
    execute immediate SqlText('Merge',ia.INDEXSCHEMA,ia.INDEXNAME) using NewVal.Deal,NewVal.Drep,RowIdToChar(Rid),NewVal.Keys;
    Return ODCIConst.Success;
  end;

  Static function ODCIINDEXSTART(Sctx In Out Sv_RccDelay,iA SYS.ODCIIndexInfo,Op SYS.ODCIPredInfo,Qi SYS.ODCIQueryInfo,Strt Number,Stop Number,Env SYS.ODCIEnv) Return Number
  is
    CurNum Integer;
  begin
    dbms_output.put_line('8');
    CurNum:=Dbms_Sql.Open_Cursor;
    Dbms_Sql.Parse(CurNum,SqlText('Select',ia.INDEXSCHEMA,ia.INDEXNAME),Dbms_Sql.Native);
    Sctx:=Sv_RccDelay(CurNum,0);
    Return ODCIConst.Success;
  end;

  member function OdciIndexFetch   (Self in out Sv_RccDelay,nRows Number,Rids out SYS.ODCIRIDLIST,Env SYS.ODCIEnv) Return Number
  is
    D      Integer;
    I      Integer;
    CurNum Integer;
    rTabs  Dbms_Sql.Varchar2_Table;
    rList  SYS.ODCIRidList:=SYS.ODCIRidList();
  begin
    dbms_output.put_line('9');
    CurNum:=Self.CurNum;
    if Self.cCount=0 then
      Dbms_Sql.Define_Array(CurNum,1,rTabs,nRows,1);
      D:=Dbms_Sql.Execute(CurNum);
    end if;
    D:=Dbms_Sql.Fetch_Rows(CurNum);
    if D=nRows then rList.Extend(D); else rList.Extend(D+1); end if;
    Dbms_Sql.Column_Value(CurNum,1,rTabs);
    for I in 1..D loop rList(I):=rTabs(I+Self.cCount); end loop;
    Self.cCount:=Self.cCount+D;
    Rids:=rList;
    Return ODCIConst.Success;
  end;

  member function OdciIndexClose   (                                                              Env SYS.ODCIEnv) Return Number
  is
    CurNum Integer;
  begin
    dbms_output.put_line('10');
    CurNum:=Self.CurNum;
    Dbms_Sql.Close_Cursor(CurNum);
    Return ODCIConst.Success;
  end;
end;
/
create or replace function Sv_RccDelayExe(Col SV_RccKey,IndexCtx in SYS.ODCIIndexCtx,ScanCtx in out Sv_RccDelay,ScanFlg in Number) return Number
is
begin
  dbms_output.put_line('12');
  if(IndexCtx.IndexInfo is Not Null)then
    if(ScanCtx is Null) then ScanCtx:=Sv_RccDelay(0,0); end if;
    if(ScanFlg=ODCICONST.RegularCall)then return 1; else return 0; end if;
  else
    Raise_Application_Error(-20101,'A column that has a domain index of Position indextype must be the first argument');
  end if;
end;
/
create or replace operator Sv_RccDelayValue binding(Sv_RccKey) return number 
with index context, scan context Sv_RccDelay using Sv_RccDelayExe;
create indextype Sv_RccDelayType  for Sv_RccDelayValue(Sv_RccKey) using Sv_RccDelay;
create index     Sv_RccDelayIndex on  Sv_Rcc(Sv_RccKey(DEAL,DREP,KEY1+KEY2)) indextype is Sv_RccDelayType;
select * 
  from Sv_Rcc
 where Sv_RccDelayValue(Sv_RccKey(DEAL,DREP,KEY1+KEY2))=1
   and DEAL='Fox'
 order by 
       AMOUNT desc,
       DEAL