15 Power Demand Cartridge Example

Example 15-7 Creating power_idxtype_im Object Type for Power Demand Cartridge

CREATE OR REPLACE TYPE power_idxtype_im AS OBJECT
(
  curnum 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 ODCIIndexStart(sctx IN OUT power_idxtype_im,
                                 ia sys.ODCIIndexInfo,
                                 op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
                                 strt NUMBER, stop NUMBER,
                                 cmppos NUMBER, cmpval NUMBER, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexStart(sctx IN OUT power_idxtype_im,
                                 ia sys.ODCIIndexInfo,
                                 op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
                                 strt NUMBER, stop NUMBER,
                                 cmpval NUMBER, env sys.ODCIEnv)
     RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT sys.ODCIRidList, 
     env sys.ODCIEnv) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexClose (env sys.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  newval PowerDemand_Typ, env sys.ODCIEnv)
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  oldval PowerDemand_Typ, env sys.ODCIEnv)
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  oldval PowerDemand_Typ, 
                                  newval PowerDemand_Typ, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo, 
                                       expversion VARCHAR2, 
                                       newblock OUT PLS_INTEGER, 
                                       env sys.ODCIEnv) 
     RETURN VARCHAR2
);
/

Example 15-8 Registering Interface and Index Functions in Power Demand Cartridge

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

Example 15-9 Registering ODCIIndexCreate() for Power Demand Cartridge

STATIC FUNCTION ODCIIndexCreate (
  ia sys.ODCIIndexInfo,
  parms VARCHAR2,
  env sys.ODCIEnv)
RETURN NUMBER IS
  i INTEGER;
  r ROWID;
  p NUMBER;
  v NUMBER;
  stmt1 VARCHAR2(1000);
  stmt2 VARCHAR2(1000);
  stmt3 VARCHAR2(1000);
  cnum1 INTEGER;
  cnum2 INTEGER;
  cnum3 INTEGER;
junk NUMBER;

The SQL statement to create the table for the index data is constructed and executed. The table includes the ROWID of the base table, r, the cell position number (cpos) in the grid from 1 to 100, and the power demand value in that cell (cval).

BEGIN
  -- Construct the SQL statement.
  stmt1 := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||'_pidx' ||
      '( r ROWID, cpos NUMBER, cval NUMBER)';

  -- Dump the SQL statement.
  dbms_output.put_line('ODCIIndexCreate>>>>>');
  sys.ODCIIndexInfoDump(ia);
  dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt1);

  -- Execute the statement.
  cnum1 := dbms_sql.open_cursor;
  dbms_sql.parse(cnum1, stmt1, dbms_sql.native);
  junk := dbms_sql.execute(cnum1);
  dbms_sql.close_cursor(cnum1);

The function populates the index by inserting rows into the table. The function "unnests" the VARRAY attribute and inserts a row for each cell into the table. Thus, each 10 X 10 grid (10 rows, 10 values for each row) becomes 100 rows in the table (one row for each cell).

  -- Now populate the table.
  stmt2 := ' INSERT INTO '|| ia.IndexSchema || '.' || ia.IndexName || '_pidx' ||
      ' SELECT :rr, ROWNUM, column_value FROM THE' || ' (SELECT CAST (P.'||
      ia.IndexCols(1).ColName||'.CellDemandValues AS NumTab_Typ)'|| ' FROM ' ||
      ia.IndexCols(1).TableSchema || '.' || ia.IndexCols(1).TableName || ' P' ||
      ' WHERE P.ROWID = :rr)';
 
  -- Execute the statement.
  dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt2);
 
  -- Parse the statement.
  cnum2 := dbms_sql.open_cursor;
  dbms_sql.parse(cnum2, stmt2, dbms_sql.native);
 
  stmt3 := 'SELECT ROWID FROM '|| ia.IndexCols(1).TableSchema || '.' ||
      ia.IndexCols(1).TableName;
  dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt3);
  cnum3 := dbms_sql.open_cursor;
  dbms_sql.parse(cnum3, stmt3, dbms_sql.native);
  dbms_sql.define_column_rowid(cnum3, 1, r);   
  junk := dbms_sql.execute(cnum3);
 
   WHILE dbms_sql.fetch_rows(cnum3) > 0 LOOP
    -- Get column values of the row. --
    dbms_sql.column_value_rowid(cnum3, 1, r);
    -- Bind the row into the cursor for the next insert. --
    dbms_sql.bind_variable_rowid(cnum2, ':rr', r);
    junk := dbms_sql.execute(cnum2);
  END LOOP;

The function concludes by closing the cursors and returning a success status.

  dbms_sql.close_cursor(cnum2);
  dbms_sql.close_cursor(cnum3);
  RETURN ODCICONST.SUCCESS;
END ODCIInexCreate;

Example 15-10 Registering ODCIIndexDrop() for Power Demand Cartridge

STATIC FUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv)
RETURN NUMBER IS
  stmt VARCHAR2(1000);
  cnum INTEGER;
  junk INTEGER;
BEGIN
  -- Construct the SQL statement.
  stmt := 'drop table ' || ia.IndexSchema || '.' || ia.IndexName || '_pidx';
  
  dbms_output.put_line('ODCIIndexDrop>>>>>');
  sys.ODCIIndexInfoDump(ia);
  dbms_output.put_line('ODCIIndexDrop>>>>>'||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 ODCIIndexDrop;

Example 15-11 Registering ODCIIndexStart() for Power Demand Cartridge

STATIC FUNCTION ODCIIndexStart(
  sctx IN OUT power_idxtype_im, 
  ia sys.ODCIIndexInfo,
  op sys.ODCIPredInfo, 
  qi sys.ODCIQueryInfo,
  strt NUMBER, stop NUMBER,
  cmppos NUMBER, 
  cmpval NUMBER, 
  env sys.ODCIEnv ) 
RETURN NUMBER IS
  cnum INTEGER;
  rid ROWID;
  nrows INTEGER;
  relop VARCHAR2(2);
  stmt VARCHAR2(1000);
BEGIN
  dbms_output.put_line('ODCIIndexStart>>>>>');
  sys.ODCIIndexInfoDump(ia);
  sys.ODCIPredInfoDump(op);
  dbms_output.put_line('start key : '||strt);
  dbms_output.put_line('stop key : '||stop);
  dbms_output.put_line('compare position : '||cmppos);
  dbms_output.put_line('compare value : '||cmpval);

The function checks for errors in the predicate.

  -- Take care of some error cases.
  -- The only predicates in which btree operators can appear are
  --    op() = 1     OR    op() = 0
  if (strt != 1) and (strt != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
  END if;
 
  if (stop != 1) and (stop != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
  END if;

The function generates the SQL statement to be executed. It determines the operator name and the lower and upper index value bounds (the start and stop keys). The start and stop keys can both be 1 (= TRUE) or both be 0 (= FALSE).

  -- Generate the SQL statement to be executed.
  -- First, figure out the relational operator needed for the statement.
  -- Take into account the operator name and the start and stop keys. For now, 
  -- the start and stop keys can both be 1 (= TRUE) or both be 0 (= FALSE).
  if op.ObjectName = 'POWER_EQUALS' then
    if strt = 1 then 
      relop := '=';
    else
      relop := '!=';
    end if;
  elsif op.ObjectName = 'POWER_LESSTHAN' then
    if strt = 1 then 
      relop := '<';
    else
      relop := '>=';
    end if;
  elsif op.ObjectName = 'POWER_GREATERTHAN' then
    if strt = 1 then 
      relop := '>';
    else
      relop := '<=';
    end if;
  else
    raise_application_error(-20101, 'Unsupported operator');
  end if;
 
  stmt := 'select r from '||ia.IndexSchema||'.'||ia.IndexName||'_pidx'||
      ' where cpos '|| '=' ||''''||cmppos||''''|| ' and cval ' ||relop||''''||
      cmpval||'''';
 
  dbms_output.put_line('ODCIIndexStart>>>>>' || stmt);
   cnum := dbms_sql.open_cursor;
  dbms_sql.parse(cnum, stmt, dbms_sql.native);
  dbms_sql.define_column_rowid(cnum, 1, rid);   
  nrows := dbms_sql.execute(cnum);

The function stores the cursor number in the context, which is used by the ODCIIndexFetch function, and sets a success return status.

  -- Set context as the cursor number.
  stcx := power_idxtype_im(cnum);
 
  -- Return success.
  RETURN ODCICONST.SUCCESS;
END ODCIIndexStart;

Example 15-12 Registering ODCIIndexStart() for Any Queries for Power Demand Cartridge

STATIC FUNCTION ODCIIndexStart(
  sctx IN OUT power_idxtype_im, 
  ia sys.ODCIIndexInfo,
  op sys.ODCIPredInfo, 
  qi sys.ODCIQueryInfo,
  strt NUMBER, 
  stop NUMBER,
  cmpval NUMBER, 
  env sys.ODCIEnv ) 
RETURN NUMBER IS
  cnum INTEGER;
  rid ROWID;
  nrows INTEGER;
  relop VARCHAR2(2);
  stmt VARCHAR2(1000);
BEGIN
  dbms_output.put_line('ODCIIndexStart>>>>>');
  sys.ODCIIndexInfoDump(ia);
  sys.ODCIPredInfoDump(op);
  dbms_output.put_line('start key : '||strt);
  dbms_output.put_line('stop key : '||stop);
  dbms_output.put_line('compare value : '||cmpval);

The function checks for errors in the predicate.

  -- Take care of some error cases.
  -- The only predicates in which btree operators can appear are
  --    op() = 1     OR    op() = 0
  if (strt != 1) and (strt != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
  END if;
 
  if (stop != 1) and (stop != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
  END if;

The function generates the SQL statement to be executed. It determines the operator name and the lower and upper index value bounds (the start and stop keys). The start and stop keys can both be 1 (= TRUE) or both be 0 (= FALSE).

  -- Generate the SQL statement to be executed.
  -- First, figure out the relational operator needed for the statement.
  -- Take into account the operator name and the start and stop keys. For now, 
  -- the start and stop keys can both be 1 (= TRUE) or both be 0 (= FALSE).
  if op.ObjectName = 'POWER_EQUALSANY' then
    relop := '=';
  elsif op.ObjectName = 'POWER_LESSTHANANY' then
    relop := '<';
  elsif op.ObjectName = 'POWER_GREATERTHANANY' then
    relop := '>';
  else
    raise_application_error(-20101, 'Unsupported operator');
  end if;
 
  -- This statement returns the qualifying rows for the TRUE case.
  stmt := 'select distinct r from '||ia.IndexSchema||'.'||ia.IndexName||'_pidx'||'
      where cval '||relop||''''||cmpval||'''';
  -- In the FALSE case, we must find the  complement of the rows.
  if (strt = 0) then
    stmt := 'select distinct r from '||ia.IndexSchema||'.'||ia.IndexName||
          '_pidx'||' minus '||stmt;
  end if;
 
  dbms_output.put_line('ODCIIndexStart>>>>>' || stmt);
  cnum := dbms_sql.open_cursor;
  dbms_sql.parse(cnum, stmt, dbms_sql.native);
  dbms_sql.define_column_rowid(cnum, 1, rid);   
  nrows := dbms_sql.execute(cnum);

The function stores the cursor number in the context, which is used by the ODCIIndexFetch() function, and sets a success return status.

  -- Set context as the cursor number.
  self := power_idxtype_im(cnum);
 
  -- Return success.
  RETURN ODCICONST.SUCCESS;
END ODCIIndexStart;

Example 15-13 Registering ODCIIndexFetch() for Power Demand Cartridge

MEMBER FUNCTION ODCIIndexFetch(
  nrows NUMBER, 
  rids OUT sys.ODCIRidList, 
  env sys.ODCIEnv)
RETURN NUMBER IS
  cnum INTEGER;
  idx INTEGER := 1;
  rlist sys.ODCIRidList := sys.ODCIRidList();
  done boolean := FALSE;

The function loops through the collection of rows selected by the ODCIIndexStart() function, using the same cursor number, cnum, as in the ODCIIndexStart() function, and returns the ROWIDs.

BEGIN
  dbms_output.put_line('ODCIIndexFetch>>>>>');
  dbms_output.put_line('Nrows : '||round(nrows));
 
  cnum := self.curnum;
 
  WHILE not done LOOP
    if idx > nrows then
       done := TRUE;
    else
      rlist.extEND;
      if dbms_sql.fetch_rows(cnum) > 0 then
        dbms_sql.column_value_rowid(cnum, 1, rlist(idx));
        idx := idx + 1;
      else
        rlist(idx) := null;
        done := TRUE;
      END if;
    END if;   
  END LOOP;
 
  rids := rlist;
  RETURN ODCICONST.SUCCESS;
END ODCIIndexFetch;

Example 15-14 Registering ODCIIndexStart() for Power Demand Cartridge

MEMBER FUNCTION ODCIIndexClose (env sys.ODCIEnv) 
RETURN NUMBER IS 
  cnum INTEGER;
BEGIN
  dbms_output.put_line('ODCIIndexClose>>>>>');
 
  cnum := self.curnum;
  dbms_sql.close_cursor(cnum);
  RETURN ODCICONST.SUCCESS;
END ODCIIndexClose;

Example 15-15 Registering ODCIIndexInsert() for Power Demand Cartridge

STATIC FUNCTION ODCIIndexInsert(
  ia sys.ODCIIndexInfo, 
  rid VARCHAR2, 
  newval PowerDemand_Typ, 
  env sys.ODCIEnv) 
RETURN NUMBER AS 
  cid INTEGER; 
  i BINARY_INTEGER;
  nrows INTEGER;
  stmt VARCHAR2(1000);
BEGIN 
  dbms_output.put_line(' ');
  dbms_output.put_line('ODCIIndexInsert>>>>>'||' TotGridDemand= '||
      newval.TotGridDemand ||' MaxCellDemand= '||newval.MaxCellDemand ||
      ' MinCellDemand= '||newval.MinCellDemand) ;
  sys.ODCIIndexInfoDump(ia); 
      
  -- Construct the statement.
  stmt := ' INSERT INTO '|| ia.IndexSchema || '.' || ia.IndexName || '_pidx' ||
      ' VALUES (:rr, :pos, :val)';
  
  -- Execute the statement.
  dbms_output.put_line('ODCIIndexInsert>>>>>'||stmt);
  -- Parse the statement.
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, stmt, dbms_sql.native);
  dbms_sql.bind_variable_rowid(cid, ':rr', rid);
      
  -- Iterate over the rows of the Varray and insert them.
  i := newval.CellDemandValues.FIRST;   
  WHILE i IS NOT NULL LOOP
    -- Bind the row into the cursor for insert.
    dbms_sql.bind_variable(cid, ':pos', i);   
    dbms_sql.bind_variable(cid, ':val', newval.CellDemandValues(i));
    -- Execute.
    nrows := dbms_sql.execute(cid);
    dbms_output.put_line('ODCIIndexInsert>>>>>('||'RID'||' , '||i|| ' , '||
           newval.CellDemandValues(i)|| ')');
    i := newval.CellDemandValues.NEXT(i);
  END LOOP;

  dbms_sql.close_cursor(cid);
  RETURN ODCICONST.SUCCESS;
END ODCIIndexInsert;

Example 15-16 Registering ODCIIndexDelete() for Power Demand Cartridge

STATIC FUNCTION ODCIIndexDelete(
  ia sys.ODCIIndexInfo, 
  rid VARCHAR2,
  oldval PowerDemand_Typ,
  env sys.ODCIEnv) 
RETURN NUMBER AS 
  cid INTEGER; 
  stmt VARCHAR2(1000);
  nrows INTEGER; 
BEGIN 
  dbms_output.put_line(' ');
  dbms_output.put_line('ODCIIndexDelete>>>>>'||' TotGridDemand= '||
      oldval.TotGridDemand ||' MaxCellDemand= '||oldval.MaxCellDemand ||
      ' MinCellDemand= '||oldval.MinCellDemand) ;
  sys.ODCIIndexInfoDump(ia); 
 
  -- Construct the statement.
  stmt := ' DELETE FROM '|| ia.IndexSchema || '.' ||ia.IndexName|| '_pidx' || 
      ' WHERE r=:rr';
  dbms_output.put_line('ODCIIndexDelete>>>>>'||stmt);
 
  -- Parse and execute the statement.
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, stmt, dbms_sql.native);
  dbms_sql.bind_variable_rowid(cid, ':rr', rid);
  nrows := dbms_sql.execute(cid);     
  dbms_sql.close_cursor(cid);
 
  RETURN ODCICONST.SUCCESS;
END ODCIIndexDelete;

Example 15-17 Registering ODCIIndexUpdate() for Power Demand Cartridge

STATIC FUNCTION ODCIIndexUpdate(
  ia sys.ODCIIndexInfo, 
  rid VARCHAR2, 
  oldval PowerDemand_Typ, 
  newval PowerDemand_Typ, 
  env sys.ODCIEnv) 
RETURN NUMBER AS 
  cid INTEGER; 
  cid2 INTEGER; 
  stmt VARCHAR2(1000);
  stmt2 VARCHAR2(1000);
  nrows INTEGER; 
  i NUMBER;
BEGIN 
  dbms_output.put_line(' ');
  dbms_output.put_line('ODCIIndexUpdate>>>>> Old'||' TotGridDemand= '||
      oldval.TotGridDemand||' MaxCellDemand= '||oldval.MaxCellDemand ||
      ' MinCellDemand= '||oldval.MinCellDemand) ;
  dbms_output.put_line('ODCIIndexUpdate>>>>> New'||' TotGridDemand= '||
      newval.TotGridDemand ||' MaxCellDemand= '||newval.MaxCellDemand ||
      ' MinCellDemand= '||newval.MinCellDemand) ;
  sys.ODCIIndexInfoDump(ia); 

  -- Delete old entries.
  stmt := ' DELETE FROM '||ia.IndexSchema ||'.'||ia.IndexName||'_pidx'|| 
      ' WHERE r=:rr';
  dbms_output.put_line('ODCIIndexUpdate>>>>>'||stmt);
  
  -- Parse and execute the statement.
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, stmt, dbms_sql.native);
  dbms_sql.bind_variable_rowid(cid, ':rr', rid);
  nrows := dbms_sql.execute(cid);     
  dbms_sql.close_cursor(cid);
 
  -- Insert new entries.
  stmt2 := ' INSERT INTO '||ia.IndexSchema||'.'||ia.IndexName||'_pidx'||
      ' VALUES (:rr, :pos, :val)';
  dbms_output.put_line('ODCIIndexUpdate>>>>>'||stmt2);
 
  -- Parse and execute the statement.
  cid2 := dbms_sql.open_cursor;
  dbms_sql.parse(cid2, stmt2, dbms_sql.native);
  dbms_sql.bind_variable_rowid(cid2, ':rr', rid);
     
  -- Iterate over the rows of the Varray and insert them.
  i := newval.CellDemandValues.FIRST;   
  WHILE i IS NOT NULL LOOP
    -- Bind the row into the cursor for insert.
    dbms_sql.bind_variable(cid2, ':pos', i);   
    dbms_sql.bind_variable(cid2, ':val', newval.CellDemandValues(i));
    nrows := dbms_sql.execute(cid2);
    dbms_output.put_line('ODCIIndexUpdate>>>>>('||'RID'||' , '||i ||' , '||
         newval.CellDemandValues(i)|| ')');
    i := newval.CellDemandValues.NEXT(i);
  END LOOP;
  dbms_sql.close_cursor(cid2);
 
  RETURN ODCICONST.SUCCESS;
END ODCIIndexUpdate;

ODCIIndexUpdate is the last method defined in the CREATE TYPE BODY statement, which ends as follows:

END;
/

Example 15-18 Registering ODCIIndexGetMetadata() for Power Demand Cartridge

STATIC FUNCTION ODCIIndexGetMetadata(
  ia sys.ODCIIndexInfo, 
  expversion VARCHAR2, 
  newblock OUT PLS_INTEGER, 
  env sys.ODCIEnv) 
RETURN VARCHAR2 IS 
 
BEGIN 
  -- Let getversion do all the work since it has to maintain state across calls. 
 
  RETURN power_pkg.getversion (ia.IndexSchema, ia.IndexName, newblock); 
 
  EXCEPTION 
    WHEN OTHERS THEN 
      RAISE; 
 
END ODCIIndexGetMetaData; 
 

Example 15-19 Creating Package power_pkg for the Power Demand Cartridge

CREATE OR REPLACE PACKAGE power_pkg AS  
  FUNCTION getversion(
    idxschema IN VARCHAR2, 
    idxname IN VARCHAR2,
    newblock OUT PLS_INTEGER) 
  RETURN VARCHAR2; 

  PROCEDURE checkversion (
  version IN VARCHAR2);  
END power_pkg; 
/ 
SHOW ERRORS; 
 
CREATE OR REPLACE PACKAGE BODY power_pkg AS  
  -- iterate is a package-level variable used to maintain state across calls 
  -- by Export in this session. 
  
  iterate NUMBER := 0;  
  
  FUNCTION getversion(
    idxschema IN VARCHAR2, 
    idxname IN VARCHAR2,  
    newblock OUT PLS_INTEGER) 
  RETURN VARCHAR2 IS  
  
  BEGIN  
  
  -- We are generating only one PL/SQL block consisting of one line of code.
    newblock := 1; 
  
    IF iterate = 0 THEN  
      -- Increment iterate so we'll know we're done next time we're called. 
      iterate := iterate + 1;  
  
      -- Return a string that calls checkversion with a version 'V1.0' 
      -- Note that export adds the surrounding BEGIN/END pair to form the anon. 
      -- block... we don't have to. 
  
      RETURN 'power_pkg.checkversion(''V1.0'');';  
    ELSE  
      -- reset iterate for next index  
      iterate := 0;  
      -- Return a 0-length string; we won't be called again for this index. 
      RETURN '';  
    END IF;  
  END getversion;  
  
  PROCEDURE checkversion (version IN VARCHAR2) 
  IS  
    wrong_version EXCEPTION; 
 
  BEGIN  
    IF version != 'V1.0' THEN 
      RAISE wrong_version; 
    END IF; 
  END checkversion;  

END power_pkg;

Example 15-22 Creating power_statistics Object Type Definition for Power Demand Cartridge

CREATE OR REPLACE TYPE power_statistics AS OBJECT
(
  curnum NUMBER,
  STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList) 
    RETURN NUMBER,
  STATIC FUNCTION ODCIStatsCollect(col sys.ODCIColInfo, 
      options sys.ODCIStatsOptions, rawstats OUT RAW, env sys.ODCIEnv) 
    RETURN NUMBER,
  STATIC FUNCTION ODCIStatsDelete(col sys.ODCIColInfo, env sys.ODCIEnv) 
      RETURN NUMBER,
  STATIC FUNCTION ODCIStatsCollect(ia sys.ODCIIndexInfo,
      options sys.ODCIStatsOptions, rawstats OUT RAW, env sys.ODCIEnv) 
    RETURN NUMBER,
  STATIC FUNCTION ODCIStatsDelete(ia sys.ODCIIndexInfo, env sys.ODCIEnv) 
      RETURN NUMBER,
  STATIC FUNCTION ODCIStatsSelectivity(pred sys.ODCIPredInfo,
      sel OUT NUMBER, args sys.ODCIArgDescList, strt NUMBER, stop NUMBER,
      object PowerDemand_Typ, cell NUMBER, value NUMBER, env sys.ODCIEnv) 
    RETURN NUMBER,
  PRAGMA restrict_references(ODCIStatsSelectivity, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsSelectivity(pred sys.ODCIPredInfo, sel OUT NUMBER, 
      args sys.ODCIArgDescList, strt NUMBER, stop NUMBER, object PowerDemand_Typ,
      value NUMBER, env sys.ODCIEnv) 
    RETURN NUMBER,
  PRAGMA restrict_references(ODCIStatsSelectivity, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo, sel NUMBER, 
      cost OUT sys.ODCICost, qi sys.ODCIQueryInfo, pred sys.ODCIPredInfo, 
      args sys.ODCIArgDescList, strt NUMBER, stop NUMBER, cmppos NUMBER, 
      cmpval NUMBER, env sys.ODCIEnv)
    RETURN NUMBER,
  PRAGMA restrict_references(ODCIStatsIndexCost, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo, sel NUMBER, 
      cost OUT sys.ODCICost, qi sys.ODCIQueryInfo, pred sys.ODCIPredInfo, 
      args sys.ODCIArgDescList, strt NUMBER, stop NUMBER, cmpval NUMBER, 
      env sys.ODCIEnv) 
    RETURN NUMBER,
  PRAGMA restrict_references(ODCIStatsIndexCost, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo, 
      cost OUT sys.ODCICost, args sys.ODCIArgDescList, object PowerDemand_Typ,
      cell NUMBER, value NUMBER, env sys.ODCIEnv) 
    RETURN NUMBER,
  PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
      cost OUT sys.ODCICost, args sys.ODCIArgDescList, object PowerDemand_Typ,
      value NUMBER, env sys.ODCIEnv) 
    RETURN NUMBER,
  PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS)
  STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
      cost OUT sys.ODCICost, args sys.ODCIArgDescList, object PowerDemand_Typ,
      cell NUMBER, value NUMBER, env sys.ODCIEnv)
    RETURN NUMBER
);
/

Example 15-23 Registering interfaces and Statistics Functions for Power Demand Cartridge

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

Example 15-24 Registering ODCIStatsCollect() for Power Demand Cartridge

STATIC FUNCTION ODCIStatsCollect(
  col sys.ODCIColInfo,
  options sys.ODCIStatsOptions,
  rawstats OUT RAW, 
  env sys.ODCIEnv)
RETURN NUMBER IS
  cnum                INTEGER;
  stmt                VARCHAR2(1000);
  junk                INTEGER;
  cval                NUMBER;
  colname             VARCHAR2(30) := rtrim(ltrim(col.colName, '"'), '"');
  statsexists         BOOLEAN := FALSE;
  pdemands            PowerDemand_Tab%ROWTYPE;
  user_defined_stats  PowerCartUserStats%ROWTYPE;

  CURSOR c1(tname VARCHAR2, cname VARCHAR2) IS
    SELECT * FROM PowerCartUserStats
    WHERE tab = tname AND col = cname;
  CURSOR c2 IS
    SELECT * FROM PowerDemand_Tab;

  BEGIN
    sys.ODCIColInfoDump(col);
    sys.ODCIStatsOptionsDump(options);

    IF (col.TableSchema IS NULL OR col.TableName IS NULL OR col.ColName IS NULL)
    THEN
      RETURN ODCIConst.Error;
    END IF;

    dbms_output.put_line('ODCIStatsCollect>>>>>');
    dbms_output.put_line('**** Analyzing column '||col.TableSchema|| '.' ||
        col.TableName|| '.' || col.ColName);

    -- Check if statistics exist for this column
    FOR user_defined_stats IN c1(col.TableName, colname) LOOP
      statsexists := TRUE;
      EXIT;
    END LOOP;

The function checks whether statistics for this column exist. If so, it initializes them to NULL; otherwise, it creates statistics for each of the 100 cells and initializes them to NULL.

    IF not statsexists THEN
      -- column statistics don't exist; create entries for each of the 100 cells
      cnum := dbms_sql.open_cursor;
      FOR i in 1..100 LOOP
        stmt := 'INSERT INTO PowerCartUserStats VALUES( '||''''|| col.TableName ||
            ''', '||''''||colname||''', '||to_char(i)||', '||'NULL, NULL, NULL)';
        dbms_sql.parse(cnum, stmt, dbms_sql.native);
        junk := dbms_sql.execute(cnum);
      END LOOP;
      dbms_sql.close_cursor(cnum);
    ELSE
      -- column statistics exist; initialize to NULL
      cnum := dbms_sql.open_cursor;
      stmt := 'UPDATE PowerCartUserStats'||
          ' SET lo = NULL, hi = NULL, nrows = NULL'||' WHERE tab = '||
          col.TableName||' AND col = '||colname;
      dbms_sql.parse(cnum, stmt, dbms_sql.native);
      junk := dbms_sql.execute(cnum);
      dbms_sql.close_cursor(cnum);
    END IF;

The function collects statistics for the column by reading rows from the table that is being analyzed. This is done by constructing and executing a SQL statement.

    -- For each cell position, the following statistics are collected:
    --   maximum value
    --   minimum value
    --   number of rows (excluding NULLs)
    cnum := dbms_sql.open_cursor;
    FOR i in 1..100 LOOP
      FOR pdemands IN c2 LOOP
        IF i BETWEEN pdemands.sample.CellDemandValues.FIRST AND
            pdemands.sample.CellDemandValues.LAST THEN
          cval := pdemands.sample.CellDemandValues(i);
          stmt := 'UPDATE PowerCartUserStats SET '|| 'lo = least(' || 'NVL(' ||
              to_char(cval)||', lo), '||'NVL('||'lo, '||to_char(cval)||')), '||
              'hi = greatest('||'NVL('||to_char(cval)||', hi), '||'NVL('||
              'hi, '||to_char(cval)||')), '||
              'nrows = decode(nrows, NULL, decode('||to_char(cval)||
              ', NULL, NULL, 1), decode('||to_char(cval)|| 
              ', NULL, nrows, nrows+1)) '||'WHERE cpos = '||to_char(i)||
              ' AND tab = '''||col.TableName||''''||' AND col = '''||colname||
              '''';
          dbms_sql.parse(cnum, stmt, dbms_sql.native);
          junk := dbms_sql.execute(cnum);
        END IF;
      END LOOP;
    END LOOP;

The function concludes by closing the cursor and returning a success status.

    dbms_sql.close_cursor(cnum);
    rawstats := NULL;
    return ODCIConst.Success;

  END ODCIStatsCollect;

Example 15-25 Registering ODCIStatsDelete() for Power Demand Cartridge

STATIC FUNCTION ODCIStatsDelete(
  col sys.ODCIColInfo, 
  env sys.ODCIEnv)
RETURN NUMBER IS
  cnum                INTEGER;
  stmt                VARCHAR2(1000);
  junk                INTEGER;
  colname             VARCHAR2(30) := rtrim(ltrim(col.colName, '"'), '"');
  statsexists         BOOLEAN := FALSE;
  user_defined_stats  PowerCartUserStats%ROWTYPE;

  CURSOR c1(tname VARCHAR2, cname VARCHAR2) IS
    SELECT * FROM PowerCartUserStats
    WHERE tab = tname AND col = cname;
  BEGIN
  sys.ODCIColInfoDump(col);

  IF (col.TableSchema IS NULL OR col.TableName IS NULL OR col.ColName IS NULL)
  THEN
    RETURN ODCIConst.Error;
  END IF;

  dbms_output.put_line('ODCIStatsDelete>>>>>');
  dbms_output.put_line('**** Analyzing (delete) column '|| col.TableSchema|| 
      '.' ||col.TableName||'.'||col.ColName);

Example 15-26 Registering ODCIStatsCollect() for Power Demand Cartridge

STATIC FUNCTION ODCIStatsCollect (
  ia sys.ODCIIndexInfo,
  options sys.ODCIStatsOptions, 
  rawstats OUT RAW, 
  env sys.ODCIEnv)
RETURN NUMBER IS
  stmt                VARCHAR2(1000);
 
BEGIN
  -- To analyze a domain index, analyze the table that implements the index
  sys.ODCIIndexInfoDump(ia);
  sys.ODCIStatsOptionsDump(options);

  stmt := 'dbms_stats.gather_table_stats('
         || '''' || ia.IndexSchema || ''', '
         || '''' || ia.IndexName || '_pidx' || ''');';
  dbms_output.put_line('**** Analyzing index '
         || ia.IndexSchema || '.' || ia.IndexName);
  dbms_output.put_line('SQL Statement: ' || stmt); 
  EXECUTE IMMEDIATE 'BEGIN ' || stmt || ' END;';
  rawstats := NULL;

  RETURN ODCIConst.Success;
END ODCIStatsCollect;

Example 15-27 Registering ODCIStatsDelete() for Domain Indexes in Power Demand Cartridge

STATIC FUNCTION ODCIStatsDelete(
  ia sys.ODCIIndexInfo,
  env sys.ODCIEnv)
RETURN NUMBER IS
  stmt                VARCHAR2(1000);
BEGIN
  -- To delete statistics for a domain index, delete the statistics for the
  -- table implementing the index
  sys.ODCIIndexInfoDump(ia);
  stmt := 'dbms_stats.delete_table_stats('|| '''' || ia.IndexSchema || ''', '
      || '''' || ia.IndexName || '_pidx' || ''');';
  dbms_output.put_line('**** Analyzing (delete) index '||ia.IndexSchema||'.'||
      ia.IndexName);
  dbms_output.put_line('SQL Statement: ' || stmt);

  EXECUTE IMMEDIATE 'BEGIN ' || stmt || ' END;';
  RETURN ODCIConst.Success;
END ODCIStatsDelete;

Example 15-28 Implementing Selectivity Function for Power Demand Cartridge

CREATE FUNCTION get_selectivity(relop VARCHAR2, value NUMBER,
                                lo NUMBER, hi NUMBER, ndv NUMBER)
  RETURN NUMBER AS
  sel NUMBER := NULL;
  ndv NUMBER;
BEGIN
  -- This function computes the selectivity (as a percentage)
  -- of a predicate
  --             col <relop> <value>
  -- where <relop> is one of: =, !=, <, <=, >, >=
  --       <value> is one of: 0, 1
  -- lo and hi are the minimum and maximum values of the column in
  -- the table. This function performs a simplistic estimation of the
  -- selectivity by assuming that the range of distinct values of
  -- the column is distributed uniformly in the range lo..hi and that
  -- each distinct value occurs nrows/(hi-lo+1) times (where nrows is
  -- the number of rows).

  IF ndv IS NULL OR ndv <= 0 THEN
    RETURN 0;
  END IF;

  -- col != <value>
  IF relop = '!=' THEN
    IF value between lo and hi THEN
      sel := 1 - 1/ndv;
    ELSE
      sel := 1;
    END IF;

  -- col = <value>
  ELSIF relop = '=' THEN
    IF value between lo and hi THEN
      sel := 1/ndv;
    ELSE
      sel := 0;
    END IF;

  -- col >= <value>
  ELSIF relop = '>=' THEN
    IF lo = hi THEN
      IF value <= lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (hi-value)/(hi-lo) + 1/ndv;
    ELSIF value < lo THEN
      sel := 1;
    ELSE
      sel := 0;
    END IF;

  -- col < <value>
  ELSIF relop = '<' THEN
    IF lo = hi THEN
      IF value > lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (value-lo)/(hi-lo);
    ELSIF value < lo THEN
      sel := 0;
    ELSE
      sel := 1;
    END IF;

  -- col <= <value>
  ELSIF relop = '<=' THEN
    IF lo = hi THEN
      IF value >= lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (value-lo)/(hi-lo) + 1/ndv;
    ELSIF value < lo THEN
      sel := 0;
    ELSE
      sel := 1;
    END IF;

  -- col > <value>
  ELSIF relop = '>' THEN
    IF lo = hi THEN
      IF value < lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (hi-value)/(hi-lo);
    ELSIF value < lo THEN
      sel := 1;
    ELSE
      sel := 0;
    END IF;

  END IF;

  RETURN least(100, ceil(100*sel));

END;
/

Example 15-29 Registering ODCIStatsSelectivity() for Queries for Power Demand Cartridge

  STATIC FUNCTION ODCIStatsSelectivity(pred sys.ODCIPredInfo,
     sel OUT NUMBER, args sys.ODCIArgDescList, strt NUMBER, stop NUMBER,
     object PowerDemand_Typ, cell NUMBER, value NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
     fname               varchar2(30);
     relop               varchar2(2);
     lo                  NUMBER;
     hi                  NUMBER;
     nrows               NUMBER;
     colname             VARCHAR2(30);
     statsexists         BOOLEAN := FALSE;
     stats               PowerCartUserStats%ROWTYPE;
     CURSOR c1(cell NUMBER, tname VARCHAR2, cname VARCHAR2) IS
       SELECT * FROM PowerCartUserStats
       WHERE cpos = cell
         AND tab = tname
         AND col = cname;
  BEGIN
    -- compute selectivity only when predicate is of the form:
    --      fn(col, <cell>, <value>) <relop> <val>
    -- In all other cases, return an error and let the optimizer
    -- make a guess. We also assume that the function "fn" has
    -- a return value of 0, 1, or NULL.

    -- start value
    IF (args(1).ArgType != ODCIConst.ArgLit AND
        args(1).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- stop value
    IF (args(2).ArgType != ODCIConst.ArgLit AND
        args(2).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- first argument of function
    IF (args(3).ArgType != ODCIConst.ArgCol) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- second argument of function
    IF (args(4).ArgType != ODCIConst.ArgLit AND
        args(4).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- third argument of function
    IF (args(5).ArgType != ODCIConst.ArgLit AND
        args(5).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    colname := rtrim(ltrim(args(3).colName, '"'), '"');

The first (column) argument of the function in the predicate must have statistics collected for it. If statistics have not been collected, ODCIStatsSelectivity() returns an error status.

    -- Check if the statistics table exists (we are using a
    -- user-defined table to store the user-defined statistics).
    -- Get user-defined statistics: MIN, MAX, NROWS
    FOR stats IN c1(cell, args(3).TableName, colname) LOOP
      -- Get user-defined statistics: MIN, MAX, NROWS
      lo := stats.lo;
      hi := stats.hi;
      nrows := stats.nrows;
      statsexists := TRUE;
      EXIT;
    END LOOP;

    -- If no user-defined statistics were collected, return error
    IF not statsexists THEN
      RETURN ODCIConst.Error;
    END IF;

Each Specific function predicate corresponds to an equivalent range predicate. For example, the predicate Power_EqualsSpecific_Func(col, 21, 25) = 0, which checks that the reading in cell 21 is not equal to 25, corresponds to the equivalent range predicate col[21] != 25.

The ODCIStatsSelectivity() function finds the corresponding range predicates for each Specific function predicate. There are several boundary cases where the selectivity can be immediately determined.

    -- selectivity is 0 for "fn(col, <cell>, <value>) < 0"
    IF (stop = 0 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0) THEN
      sel := 0;
      RETURN ODCIConst.Success;
    END IF;

    -- selectivity is 0 for "fn(col, <cell>, <value>) > 1"
    IF (strt = 1 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0) THEN
      sel := 0;
      RETURN ODCIConst.Success;
    END IF;

    -- selectivity is 100% for "fn(col, <cell>, <value>) >= 0"
    IF (strt = 0 AND
        bitand(pred.Flags, ODCIConst.PredExactMatch) = 0 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStart) > 0) THEN
      sel := 100;
      RETURN ODCIConst.Success;
    END IF;

    -- selectivity is 100% for "fn(col, <cell>, <value>) <= 1"
    IF (stop = 1 AND
        bitand(pred.Flags, ODCIConst.PredExactMatch) = 0 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStop) > 0) THEN
      sel := 100;
      RETURN ODCIConst.Success;
    END IF;

    -- get function name
    IF bitand(pred.Flags, ODCIConst.PredObjectFunc) > 0 THEN
      fname := pred.ObjectName;
    ELSE
      fname := pred.MethodName;
    END IF;

    -- convert prefix relational operator to infix:
    -- "Power_EqualsSpecific_Func(col, <cell>, <value>) = 1"
    -- becomes "col[<cell>] = <value>"

    --   Power_EqualsSpecific_Func(col, <cell>, <value>) = 0
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) <= 0
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) < 1
    -- can be transformed to
    --   col[<cell>] != <value>
    IF (fname LIKE upper('Power_Equals%') AND
        (stop = 0 OR
         (stop = 1 AND
          bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0))) THEN
      relop := '!=';

    --   Power_LessThanSpecific_Func(col, <cell>, <value>) = 0
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) <= 0
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) < 1
    -- can be transformed to
    --   col[<cell>] >= <value>
    ELSIF (fname LIKE upper('Power_LessThan%') AND
           (stop = 0 OR
            (stop = 1 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0))) THEN
      relop := '>=';

    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) = 0
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) <= 0
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) < 1
    -- can be transformed to
    --   col[<cell>] <= <value>
    ELSIF (fname LIKE upper('Power_GreaterThan%') AND
           (stop = 0 OR
            (stop = 1 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0))) THEN
      relop := '<=';

    --   Power_EqualsSpecific_Func(col, <cell>, <value>) = 1
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) >= 1
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) > 0
    -- can be transformed to
    --   col[<cell>] = <value>
    ELSIF (fname LIKE upper('Power_Equals%') AND
           (strt = 1 OR
            (strt = 0 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0))) THEN
      relop := '=';

    --   Power_LessThanSpecific_Func(col, <cell>, <value>) = 1
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) >= 1
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) > 0
    -- can be transformed to
    --   col[<cell>] < <value>
    ELSIF (fname LIKE upper('Power_LessThan%') AND
           (strt = 1 OR
            (strt = 0 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0))) THEN
      relop := '<';

    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) = 1
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) >= 1
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) > 0
    -- can be transformed to
    --   col[<cell>] > <value>
    ELSIF (fname LIKE upper('Power_GreaterThan%') AND
           (strt = 1 OR
            (strt = 0 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0))) THEN
      relop := '>';

    ELSE
      RETURN ODCIConst.Error;

    END IF;

After the Specific function predicate is transformed into a simple range predicate, ODCIStatsSelectivity() calls get_selectivity to compute the selectivity for the range predicate (and thus, equivalently, for the Specific function predicate). It returns with a success status.

    sel := get_selectivity(relop, value, lo, hi, nrows);
    RETURN ODCIConst.Success;
  END;

Example 15-30 Registering ODCISIndexCost() for Queries for Power Demand Cartridge

  STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo,
     sel NUMBER, cost OUT sys.ODCICost, qi sys.ODCIQueryInfo,
     pred sys.ODCIPredInfo, args sys.ODCIArgDescList,
     strt NUMBER, stop NUMBER, cmppos NUMBER, cmpval NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
  BEGIN
    -- This is the cost for queries on a specific cell; simply
    -- use the cost for queries on any cell.
    RETURN ODCIStatsIndexCost(ia, sel, cost, qi, pred, args,
                              strt, stop, cmpval, env);
  END;

Example 15-31 Registering ODCIStatsIndexCost() for Any Queries for Power Demand Cartridge

  STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo,
     sel NUMBER, cost OUT sys.ODCICost, qi sys.ODCIQueryInfo,
     pred sys.ODCIPredInfo, args sys.ODCIArgDescList,
     strt NUMBER, stop NUMBER, cmpval NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
     ixtable             VARCHAR2(40);
     numblocks           NUMBER := NULL;
     get_table           user_tables%ROWTYPE;
     CURSOR c1(tab VARCHAR2) IS
       SELECT * FROM user_tables WHERE table_name = tab;
  BEGIN
    -- This is the cost for queries on any cell.

    -- To compute the cost of a domain index, multiply the
    -- number of blocks in the table implementing the index
    -- with the selectivity

    -- Return if we don't have predicate selectivity
    IF sel IS NULL THEN
      RETURN ODCIConst.Error;
    END IF;

    cost := sys.ODCICost(NULL, NULL, NULL, NULL);

    -- Get name of table implementing the domain index
    ixtable := ia.IndexName || '_pidx';

    -- Get number of blocks in domain index
    FOR get_table IN c1(upper(ixtable)) LOOP
      numblocks := get_table.blocks;
      EXIT;
    END LOOP;

    IF numblocks IS NULL THEN
      -- Exit if there are no user-defined statistics for the index
      RETURN ODCIConst.Error;
    END IF;

    cost.CPUCost := ceil(400*(sel/100)*numblocks);
    cost.IOCost := ceil(1.5*(sel/100)*numblocks);
    RETURN ODCIConst.Success;
  END;

Example 15-32 Registering ODCIStatsFunctionCost() for Power Demand Cartridge

  STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
     cost OUT sys.ODCICost, args sys.ODCIArgDescList,
     object PowerDemand_Typ, value NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
     fname               VARCHAR2(30);
  BEGIN
    cost := sys.ODCICost(NULL, NULL, NULL, NULL);

    -- Get function name
    IF  bitand(func.Flags, ODCIConst.ObjectFunc) > 0 THEN
      fname := func.ObjectName;
    ELSE
      fname := func.MethodName;
    END IF;

    IF fname LIKE upper('Power_LessThan%') THEN
      cost.CPUCost := 5000;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSIF fname LIKE upper('Power_Equals%') THEN
      cost.CPUCost := 7000;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSIF fname LIKE upper('Power_GreaterThan%') THEN
      cost.CPUCost := 5000;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSE
      RETURN ODCIConst.Error;
    END IF;
  END;