Implementing the Index routines

1. Define an implementation type that implements the ODCIIndex interface routines.

   CREATE TYPE sbtree_im  AS OBJECT
   (
     scanctx RAW(4),
     STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
     RETURN NUMBER,  
     STATIC FUNCTION ODCIIndexCreate (ia sys.odciindexinfo, parms VARCHAR2)
     RETURN NUMBER,
     STATIC FUNCTION ODCIIndexDrop(ia sys.odciindexinfo) RETURN NUMBER,
     STATIC FUNCTION ODCIIndexInsert(ia sys.odciindexinfo, rid VARCHAR2, 
                                                       newval VARCHAR2)
                                     RETURN NUMBER, 
     STATIC FUNCTION ODCIIndexDelete(ia sys.odciindexinfo, rid VARCHAR2, 
                                                       oldval VARCHAR2)
                                     RETURN NUMBER, 
     STATIC FUNCTION ODCIIndexUpdate(ia sys.odciindexinfo, rid VARCHAR2, 
                                     oldval VARCHAR2, newval VARCHAR2)
                                     RETURN NUMBER, 
     STATIC FUNCTION ODCIIndexStart(sctx IN OUT sbtree_im, ia sys.odciindexinfo,
                            op sys.odciPredInfo, qi sys.ODCIQueryInfo,
                            strt number, stop number,
                            cmpval VARCHAR2) RETURN NUMBER,
     MEMBER FUNCTION ODCIIndexFetch(nrows number, rids OUT sys.odciridlist)
         RETURN NUMBER,
     MEMBER FUNCTION ODCIIndexClose RETURN NUMBER
   );
   
   

2. Define the implementation type body

You have a choice of implementing the index routines in any of the languages supported by Oracle. For this example, we will implement the get interfaces routine and the index definition routines in PL/SQL. The index manipulation and query routines are implemented in C.

CREATE OR REPLACE TYPE BODY sbtree_im 
IS

The get interfaces routine returns the expected interface name through its OUT parameter.

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

The ODCIIndexCreate routine creates an "index storage" table with two columns. The first column stores the VARCHAR2 indexed column value. The second column in the index table stores the rowid of the corresponding row in the base table. DBMS_SQL is used to execute the dynamically constructed SQL statement.

  STATIC FUNCTION ODCIIndexCreate (ia sys.odciindexinfo, parms VARCHAR2) 
  RETURN NUMBER
  is
   i INTEGER;
   stmt VARCHAR2(1000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
   -- construct the sql statement
     stmt := 'create table ' || ia.IndexSchema || '.' || 
       ia.IndexName || '_sbtree'  ||
       '( f1 , f2 ) as select ' ||     
       ia.IndexCols(1).ColName || ', ROWID from ' || 
       ia.IndexCols(1).TableSchema || '.' || ia.IndexCols(1).TableName;

   DBMS_OUTPUT.PUT_LINE('CREATE');
   DBMS_OUTPUT.PUT_LINE(stmt);

   -- execute the statement
   cnum := dbms_sql.open_cursor;
   DBMS_SQL.PARSE(cnum, stmt, dbms_sql.native);
   junk := dbms_sql.execute(cnum);
   DBMS_SQL.CLOSE_CURSOR(cnum);

   RETURN ODCIConst.Success;   
  END;

The ODCIIndexDrop routine drops the index storage table.

  STATIC FUNCTION ODCIIndexDrop(ia sys.odciindexinfo) RETURN NUMBER is
   stmt VARCHAR2(1000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
    -- construct the sql statement
   stmt := 'drop table ' || ia.IndexSchema || '.' || ia.IndexName || '_sbtree';

   DBMS_OUTPUT.PUT_LINE('DROP');
   DBMS_OUTPUT.PUT_LINE(stmt);

   -- execute the statement
   cnum := dbms_sql.open_cursor;
   dbms_sql.parse(cnum, stmt, dbms_sql.native);
   junk := dbms_sql.execute(cnum);
   DBMS_SQL.CLOSE_CURSOR(cnum);

   RETURN ODCIConst.Success;
  END;

The index manipulation and query routines are implemented in C. This requires some setup to be done before this statement. Specifically, you need to create a library object called extdemo2l for your compiled C code.

After the setup, the foll. statements register the implementation of the index manipulation and query routines in terms of their corresponding C functions.

Register the implementation of the ODCIIndexInsert routine.

  STATIC FUNCTION ODCIIndexInsert(ia sys.odciindexinfo, rid VARCHAR2, 
                                  newval VARCHAR2)
                                  RETURN NUMBER AS external 
    name "qxiqtbi"
    library extdemo2l
    WITH context 
    parameters (
    context,
    ia,
    ia indicator struct,
    rid,
    rid indicator,
    newval,
    newval indicator,
    RETURN ocinumber
               );

Register the implementation of the ODCIIndexDelete routine.

STATIC FUNCTION ODCIIndexDelete(ia sys.odciindexinfo, rid VARCHAR2, 
                                oldval VARCHAR2)
                                RETURN NUMBER AS external 
    name "qxiqtbd"
    library extdemo2l
    WITH context 
    parameters (
    context,
    ia,
    ia indicator struct,
    rid,
    rid indicator,
    oldval,
    oldval indicator,
    RETURN ocinumber
               );

Register the implementation of the ODCIIndexUpdate routine.

STATIC FUNCTION ODCIIndexUpdate(ia sys.odciindexinfo, rid VARCHAR2, 
                                  oldval VARCHAR2, newval VARCHAR2)
                                  RETURN NUMBER AS external 
    name "qxiqtbu"
    library extdemo2l
    WITH context 
    parameters (
    context,
    ia,
    ia indicator struct,
    rid,
    rid indicator,
    oldval, 
    oldval indicator,
    newval,
    newval indicator,
    RETURN ocinumber
               );

Register the implementation of the ODCIIndexStart routine.

  STATIC FUNCTION ODCIIndexStart(sctx in out sbtree_im, ia sys.odciindexinfo,
                         op sys.odciPredInfo, 
                         qi sys.ODCIQueryInfo, 
                         strt number, 
                         stop number,
                         cmpval VARCHAR2) 
     RETURN NUMBER as external
     name "qxiqtbs"
     library extdemo2l
     with context
     parameters (
       context,
       sctx,
       sctx INDICATOR STRUCT,
       ia,
       ia INDICATOR STRUCT,
       op,
       op INDICATOR STRUCT,
       qi,
       qi INDICATOR STRUCT,
       strt,
       strt INDICATOR,
       stop,
       stop INDICATOR,
       cmpval,
       cmpval INDICATOR,
       RETURN OCINumber
    );

Register the implementation of the ODCIIndexFetch routine.

  member function ODCIIndexFetch(nrows number, rids OUT sys.odciridlist)
   RETURN NUMBER as external
   name "qxiqtbf"
   library extdemo2l
   with context
   parameters (
     context,
     self,
     self INDICATOR STRUCT,
     nrows,
     nrows INDICATOR,
     rids,
     rids INDICATOR,
     RETURN OCINumber
   );

Register the implementation of the ODCIIndexClose routine.

  member function ODCIIndexClose RETURN NUMBER as external
   name "qxiqtbc"
   library extdemo2l
   with context
   parameters (
     context,
     self,
     self INDICATOR STRUCT,
     RETURN OCINumber
   );