-- Edit the following example(s) to set the location of the Omnidex PL/Sql interface library
-- The first example is for Windows.  The second/default is for Sun,Linux,Aix and Tru64 UNIX.
-- The third example is for HP-UX UNIX.
-- 
-- Windows: create or replace library odx_lib as 'c:\winnt\system32\odxplora.dll';
-- Sun,Linux,Tru64,AIX: create or replace library odx_lib as '$OMNIDEX_LIBS/libodxplora.so';
-- HP-UX UNIX: create or replace library odx_lib as '$OMNIDEX_LIBS/libodxplora.sl';
create or replace library odx_lib as '$OMNIDEX_LIBS/libodxplora.so';
/
show error

-- create a type of rowid for odx.selectrowid
create or replace type odx_rowid as object ( row_id varchar2(20));
/
show error

-- create a type of rowid table for odx.selectrowid
create or replace type odx_rowid_set as table of odx_rowid;
/
show error

create or replace package odx authid current_user is

  -- connect to an OmniAccess environment file
  -- MUST be disconnect using disconnect_env when finished otherwise there will
  --   be memory leak
  -- return an instance number
  function connect_env(filename in varchar2, options in varchar2 default null,
    username in varchar2 default null, pwd in varchar2 default null)
    return binary_integer;

  -- check if a connection is open
  -- return true if connection is open
  function is_connected(instance_num in binary_integer) return boolean;

  -- close the connection
  procedure disconnect_env(instance_num in binary_integer,
    options in varchar2 default null);

  -- open a new cursor
  -- MUST be closed using close_cursor when finished otherwise there will be
  --   memory leak
  -- return a cursor number
  function open_cursor(instance_num in binary_integer,
    options in varchar2 default null)  return binary_integer;

  -- check if a cursor is open
  -- return true if cursor is open
  function is_opened(cursor_num in binary_integer) return boolean;

  -- close a cursor
  procedure close_cursor(cursor_num in binary_integer,
    options in varchar2 default null);

  -- parse a sql statement
  procedure parse_sql(cursor_num in binary_integer, statement in varchar2,
    options in varchar2 default null);

  -- bind a value to the variable specified in the parsed statement
  -- only support bind value for an in variable, no support for out variable
  -- support the following variable types: number, date, varchar2
  procedure set_variable(cursor_num in binary_integer, name in varchar2,
    value in number);
  procedure set_variable(cursor_num in binary_integer, name in varchar2,
    value in date);
  procedure set_variable(cursor_num in binary_integer, name in varchar2,
    value in varchar2);

  -- define column type for a particular column in the select list
  -- valid only on SELECT statement
  -- the column type is the type of the value passed in the "column" parameter
  -- support the following column types: number, date, varchar2
  procedure set_column_type(cursor_num in binary_integer,
    position in binary_integer, column in number);
  procedure set_column_type(cursor_num in binary_integer,
    position in binary_integer, column in date);
  procedure set_column_type(cursor_num in binary_integer,
    position in binary_integer, column in varchar2);

  -- execute the sql statement
  -- return number of rows processed for INSERT, DELETE, and UPDATE statement
  -- return number of rows qualified for QUALIFY statement
  -- return 0 for other statements
  function execute_sql(cursor_num in binary_integer,
    options varchar2 default null) return binary_integer;

  -- fetch rows
  -- return the number of rows actually fetched (1 or 0)
  -- only valid for SELECT statement
  function get_row(cursor_num in binary_integer,
    options in varchar2 default null) return binary_integer;

  -- get a value of the column in the select list from a fetch
  -- support the following column value types: number, date, varchar2
  -- column value type must be the same as the one used in define_column
  procedure get_column(cursor_num in binary_integer, position in binary_integer,
    value out number);
  procedure get_column(cursor_num in binary_integer, position in binary_integer,
    value out date);
  procedure get_column(cursor_num in binary_integer, position in binary_integer,
    value out varchar2);

  -- get the last SQL function code. Use the list in OCI manual
  function get_last_sql_function_code(cursor_num in binary_integer)
    return binary_integer;

  -- get the cummulative count of rows fetched
  function get_last_row_count(cursor_num in binary_integer)
    return binary_integer;

  -- get the version number
  -- return the version number
  -- the formatted version is also returned in the parameter
  function get_version(version out varchar2, options in varchar2 default null)
    return binary_integer;

  -- select rowid based on a where clause argument
  -- return 0
  function select_rowid(cursor_num in binary_integer, tblname in varchar2,
    criteria in varchar2, orderby in varchar2 default null,
    options in varchar2 default null) return binary_integer;

  -- fetch the rowids into a temporary table
  function fetch_rowid(cursor_num in binary_integer, options in varchar2
    default null) return odx_rowid_set pipelined;

  -- insert index only
  -- return 0 or an error number
  function oainsertindex(instance_num in binary_integer, options in varchar2,
    tblname in varchar2, colnames in varchar2, buffer in varchar2,
    rowid_buf in varchar2) return binary_integer;

  -- update index only
  -- return 0 or an error number
  function oaupdateindex(instance_num in binary_integer, options in varchar2,
    tblname in varchar2, colnames in varchar2, before_buffer in varchar2,
    before_rowid in varchar2, after_buffer in varchar2, after_rowid in varchar2)
    return binary_integer;

  -- delete index only
  -- return 0 or an error number
  function oadeleteindex(instance_num in binary_integer, options in varchar2,
    tblname in varchar2, colnames in varchar2, buffer in varchar2,
    rowid_buf in varchar2) return binary_integer;

  -- set column value for insert
  procedure insert_column(instance_num in binary_integer, tblname in varchar2,
    colname in varchar2, buffer in varchar2);
  -- insert row
  procedure insert_row(instance_num in binary_integer, tblname in varchar2,
    options in varchar2 default null);
  -- insert index only
  procedure insert_index(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null);

  -- set column value for update
  procedure update_column(instance_num in binary_integer, tblname in varchar2,
    colname in varchar2, before_buffer in varchar2, after_buffer in varchar2);
  -- update row
  procedure update_row(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null);
  -- update index only
  procedure update_index(instance_num in binary_integer, tblname in varchar2,
    before_rowid in varchar2, after_rowid in varchar2, options in varchar2
    default null);

  -- set column value for delete
  procedure delete_column(instance_num in binary_integer, tblname in varchar2,
    colname in varchar2, buffer in varchar2);
  -- delete row
  procedure delete_row(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null);
  -- delete index only
  procedure delete_index(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null);

end;
/
show errors

create or replace package body odx is

  function connect_env_c(filename in varchar2, options in varchar2,
    username in varchar2, pwd in varchar2) return binary_integer as
    language c name "odxplora_connect" library odx_lib with context
    parameters(context, filename, filename indicator, filename length,
    options, options indicator, options length, username, username indicator,
    username length, pwd, pwd indicator, pwd length, return);
  function connect_env(filename in varchar2, options in varchar2 default null,
    username in varchar2 default null, pwd in varchar2 default null)
    return binary_integer as
  begin
    return connect_env_c(filename, options, username, pwd);
  end;

  function is_connected(instance_num in binary_integer) return boolean as
    language c name "odxplora_is_connected" library odx_lib
    parameters(instance_num, instance_num indicator, return);

  procedure disconnect_env_c(instance_num in binary_integer,
    options in varchar2) as language c name "odxplora_disconnect"
    library odx_lib with context parameters(context, instance_num,
    instance_num indicator, options, options indicator, options length);
  procedure disconnect_env(instance_num in binary_integer,
    options in varchar2 default null) as
  begin
    disconnect_env_c(instance_num, options);
  end;

  function open_cursor_c(instance_num in binary_integer, options in varchar2)
    return binary_integer as language c name "odxplora_opencursor"
    library odx_lib with context parameters(context, instance_num,
    instance_num indicator, options, options indicator, options length, return);
  function open_cursor(instance_num in binary_integer,
    options in varchar2 default null)  return binary_integer as
  begin
    return open_cursor_c(instance_num, options);
  end;

  function is_opened(cursor_num in binary_integer) return boolean as
    language c name "odxplora_is_opened" library odx_lib
    parameters(cursor_num, cursor_num indicator, return);

  procedure close_cursor_c(cursor_num in binary_integer, options in varchar2)
    as language c name "odxplora_closecursor" library odx_lib
    with context parameters(context, cursor_num, cursor_num indicator, options,
    options indicator, options length);
  procedure close_cursor(cursor_num in binary_integer,
    options in varchar2 default null) as
  begin
    close_cursor_c(cursor_num, options);
  end;

  procedure parse_sql_c(cursor_num in binary_integer, statement in varchar2,
    options in varchar2) as language c name "odxplora_parse_sql"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, statement, statement indicator, statement length,
    options, options indicator, options length);
  procedure parse_sql(cursor_num in binary_integer, statement in varchar2,
    options in varchar2 default null) as
  begin
    parse_sql_c(cursor_num, statement, options);
  end;

  procedure set_variable(cursor_num in binary_integer, name in varchar2,
    value in number) as language c name "odxplora_setvariable_num"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, name, name indicator, name length, value,
    value indicator);
  procedure set_variable(cursor_num in binary_integer, name in varchar2,
    value in date) as language c name "odxplora_setvariable_date"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, name, name indicator, name length, value,
    value indicator);
  procedure set_variable(cursor_num in binary_integer, name in varchar2,
    value in varchar2) as language c name "odxplora_setvariable_char"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, name, name indicator, name length, value,
    value indicator, value length);

  procedure set_column_type(cursor_num in binary_integer,
    position in binary_integer, column in number) as language c
    name "odxplora_setcoltype_num" library odx_lib with context
    parameters(context, cursor_num, cursor_num indicator, position,
    position indicator, column, column indicator);
  procedure set_column_type(cursor_num in binary_integer,
    position in binary_integer, column in date) as language c
    name "odxplora_setcoltype_date" library odx_lib with context
    parameters(context, cursor_num, cursor_num indicator, position,
    position indicator, column, column indicator);
  procedure set_column_type(cursor_num in binary_integer,
    position in binary_integer, column in varchar2) as language c
    name "odxplora_setcoltype_char" library odx_lib with context
    parameters(context, cursor_num, cursor_num indicator, position,
    position indicator, column, column indicator, column length);

  function execute_sql_c(cursor_num in binary_integer, options varchar2)
    return binary_integer as language c name "odxplora_execsql"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, options, options indicator, options length, return);
  function execute_sql(cursor_num in binary_integer,
    options varchar2 default null) return binary_integer as
  begin
    return execute_sql_c(cursor_num, options);
  end;

  function get_row_c(cursor_num in binary_integer, options in varchar2)
    return binary_integer as language c name "odxplora_getrow"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, options, options indicator, options length, return);
  function get_row(cursor_num in binary_integer, options in varchar2 default null)
    return binary_integer as
  begin
    return get_row_c(cursor_num, options);
  end;

  procedure get_column(cursor_num in binary_integer, position in binary_integer,
    value out number) as language c name "odxplora_getcolumn_num"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, position, position indicator, value, value indicator);
  procedure get_column(cursor_num in binary_integer, position in binary_integer,
    value out date) as language c name "odxplora_getcolumn_date"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, position, position indicator, value, value indicator);
  procedure get_column(cursor_num in binary_integer, position in binary_integer,
    value out varchar2) as language c name "odxplora_getcolumn_char"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator, position, position indicator, value, value indicator,
    value length, value maxlen);

  function get_last_sql_function_code(cursor_num in binary_integer)
    return binary_integer as language c name "odxplora_last_sql_function"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator);

  function get_last_row_count(cursor_num in binary_integer)
    return binary_integer as language c name "odxplora_last_row_count"
    library odx_lib with context parameters(context, cursor_num,
    cursor_num indicator);

  function get_version_c(version out varchar2, options in varchar2)
    return binary_integer as language c name "odxplora_getversion"
    library odx_lib parameters(version, version indicator, version length,
    version maxlen, options, options indicator, options length, return);
  function get_version(version out varchar2, options in varchar2 default null)
    return binary_integer as
  begin
    return get_version_c(version, options);
  end;

  function select_rowid(cursor_num in binary_integer, tblname in varchar2,
    criteria in varchar2, orderby in varchar2, options in varchar2)
    return binary_integer as
    out_rec odx_rowid := odx_rowid('                    ');
  begin
    if criteria is not null then
      parse_sql(cursor_num, 'select $rowid from ' || tblname || ' where ' ||
        criteria || ' ' || orderby, options);
    else
      parse_sql(cursor_num, 'select $rowid from ' || tblname || ' ' || orderby,
        options);
    end if;
    set_column_type(cursor_num, 1, out_rec.row_id);
    return execute_sql(cursor_num);
  end;

  function fetch_rowid_c(cursor_num in binary_integer, count in binary_integer,
    buffer out varchar2, options in varchar2) return binary_integer as
    language c name "odxplora_fetch_rowid" library odx_lib with context
    parameters(context, cursor_num, cursor_num indicator, count,
    count indicator, buffer, buffer indicator, buffer length, buffer maxlen,
    options, options indicator, options length, return);
  function fetch_rowid(cursor_num in binary_integer, options in varchar2)
    return odx_rowid_set pipelined as
    out_rec odx_rowid := odx_rowid(null);
    rid_buf varchar2(32000);
    total_rid integer;
    rid_count integer;
    rid_offset integer;
  begin
    loop
      total_rid := fetch_rowid_c(cursor_num, 1600, rid_buf, options);
      exit when total_rid = 0;
      rid_count := 0;
      rid_offset := 1;
      while rid_count < total_rid loop
        out_rec.row_id := substr(rid_buf, rid_offset, 18);
        pipe row(out_rec);
        rid_offset := rid_offset + 20;
        rid_count := rid_count + 1;
      end loop;
    end loop;
    return;
  end;

  function oainsertindex(instance_num in binary_integer, options in varchar2,
    tblname in varchar2, colnames in varchar2, buffer in varchar2,
    rowid_buf in varchar2) return binary_integer as language c
    name "odxplora_oainsertindex" library odx_lib with context parameters(
    context, instance_num, instance_num indicator, options, options indicator,
    options length, tblname, tblname indicator, tblname length, colnames,
    colnames indicator, colnames length, buffer, buffer indicator, buffer length,
    rowid_buf, rowid_buf indicator, rowid_buf length, return);

  function oaupdateindex(instance_num in binary_integer, options in varchar2,
    tblname in varchar2, colnames in varchar2, before_buffer in varchar2,
    before_rowid in varchar2, after_buffer in varchar2, after_rowid in varchar2)
    return binary_integer as language c name "odxplora_oaupdateindex"
    library odx_lib with context parameters(context, instance_num,
    instance_num indicator, options, options indicator, options length,
    tblname, tblname indicator, tblname length, colnames, colnames indicator,
    colnames length, before_buffer, before_buffer indicator,
    before_buffer length, before_rowid, before_rowid indicator,
    before_rowid length, after_buffer, after_buffer indicator,
    after_buffer length, after_rowid, after_rowid indicator, after_rowid length,
    return);

  function oadeleteindex(instance_num in binary_integer, options in varchar2,
    tblname in varchar2, colnames in varchar2, buffer in varchar2,
    rowid_buf in varchar2) return binary_integer as language c
    name "odxplora_oadeleteindex" library odx_lib with context parameters(
    context, instance_num, instance_num indicator, options, options indicator,
    options length, tblname, tblname indicator, tblname length, colnames,
    colnames indicator, colnames length, buffer, buffer indicator, buffer length,
    rowid_buf, rowid_buf indicator, rowid_buf length, return);

  procedure insert_column(instance_num in binary_integer, tblname in varchar2,
    colname in varchar2, buffer in varchar2) as language c
    name "odxplora_insert_column" library odx_lib with context parameters(
    context, instance_num, instance_num indicator, tblname, tblname indicator,
    tblname length, colname, colname indicator, colname length, buffer,
    buffer indicator, buffer length);
  procedure insert_row_c(instance_num in binary_integer, tblname in varchar2,
    options in varchar2) as language c name "odxplora_insert_row" library odx_lib
    with context parameters(context, instance_num, instance_num indicator,
    tblname, tblname indicator, tblname length, options, options indicator,
    options length);
  procedure insert_row(instance_num in binary_integer, tblname in varchar2,
    options in varchar2 default null) as
  begin
    insert_row_c(instance_num, tblname, options);
  end;
  procedure insert_index_c(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2) as language c
    name "odxplora_insert_index" library odx_lib with context parameters(
    context, instance_num, instance_num indicator, tblname, tblname indicator,
    tblname length, rowid_buf, rowid_buf indicator, rowid_buf length, options,
    options indicator, options length);
  procedure insert_index(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null) as
  begin
    insert_index_c(instance_num, tblname, rowid_buf, options);
  end;

  procedure update_column(instance_num in binary_integer, tblname in varchar2,
    colname in varchar2, before_buffer in varchar2, after_buffer in varchar2)
    as language c name "odxplora_update_column" library odx_lib with context
    parameters(context, instance_num, instance_num indicator, tblname,
    tblname indicator, tblname length, colname, colname indicator,
    colname length, before_buffer, before_buffer indicator, before_buffer length,
    after_buffer, after_buffer indicator, after_buffer length);
  procedure update_row_c(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2) as language c
    name "odxplora_update_row" library odx_lib with context parameters(context,
    instance_num, instance_num indicator, tblname, tblname indicator,
    tblname length, rowid_buf, rowid_buf indicator, rowid_buf length, options,
    options indicator, options length);
  procedure update_row(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null) as
  begin
    update_row_c(instance_Num, tblname, rowid_buf, options);
  end;
  procedure update_index_c(instance_num in binary_integer, tblname in varchar2,
    before_rowid in varchar2, after_rowid in varchar2, options in varchar2)
    as language c name "odxplora_update_index" library odx_lib with context
    parameters(context, instance_num, instance_num indicator, tblname,
    tblname indicator, tblname length, before_rowid, before_rowid indicator,
    before_rowid length, after_rowid, after_rowid indicator, after_rowid length,
    options, options indicator, options length);
  procedure update_index(instance_num in binary_integer, tblname in varchar2,
    before_rowid in varchar2, after_rowid in varchar2, options in varchar2
    default null) as
  begin
    update_index_c(instance_num, tblname, before_rowid, after_rowid, options);
  end;

  procedure delete_column(instance_num in binary_integer, tblname in varchar2,
    colname in varchar2, buffer in varchar2) as language c
    name "odxplora_delete_column" library odx_lib with context parameters(
    context, instance_num, instance_num indicator, tblname, tblname indicator,
    tblname length, colname, colname indicator, colname length, buffer,
    buffer indicator, buffer length);
  procedure delete_row_c(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2) as language c
    name "odxplora_delete_row" library odx_lib with context parameters(context,
    instance_num, instance_num indicator, tblname, tblname indicator,
    tblname length, rowid_buf, rowid_buf indicator, rowid_buf length, options,
    options indicator, options length);
  procedure delete_row(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null) as
  begin
    delete_row_c(instance_num, tblname, rowid_buf, options);
  end;
  procedure delete_index_c(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2) as language c
    name "odxplora_delete_index" library odx_lib with context parameters(
    context, instance_num, instance_num indicator, tblname, tblname indicator,
    tblname length, rowid_buf, rowid_buf indicator, rowid_buf length, options,
    options indicator, options length);
  procedure delete_index(instance_num in binary_integer, tblname in varchar2,
    rowid_buf in varchar2, options in varchar2 default null) as
  begin
    delete_index_c(instance_num, tblname, rowid_buf, options);
  end;

end;
/
show errors
grant execute on odx to public;
create or replace public synonym odx for odx;


set serveroutput on
declare
  oaversion varchar2(40);
  version varchar2(40);
  ignore number;
begin
  ignore := odx.get_version(version);
  ignore := odx.get_version(oaversion, 'OA');
  dbms_output.put_line( 'OmniAccess Interface to PL/SQL has been successfully installed');
  dbms_output.new_line;
  dbms_output.put_line( 'OmniAccess Version: ' || oaversion);
  dbms_output.put_line( 'Interface  Version: ' || version);
  dbms_output.new_line;
end;
/

