Oracle PL/SQL Omnidex Sample Code

SET serveroutput ON
 
DECLARE
  instance_num  INTEGER;
  cursor_num    INTEGER;
  child_cursor  INTEGER;
  qual_count    INTEGER;
  ignore        INTEGER;
  customer_num  NUMBER;
  company       VARCHAR2(42);
  contact       VARCHAR2(26);
  date_tickler  VARCHAR2(6);
  tickler       VARCHAR2(4);
  initials      VARCHAR2(6);
  action_info   VARCHAR2(62);
  counter       NUMBER;
BEGIN
  DBMS_OUTPUT.enable();
 
  -- opening a database from PL/SQL using CL datasource file and odxnet server
  instance_num := odx.connect_env('orders.dsn','file=dsn,networkservices=odxnet');
 
  -- opening a database from PL/SQL using OA environment file
  -- instance_num := odx.connect_env('orders.env');
 
  -- opening a database from PL/SQL using CL datasource file and odxnet server
  -- instance_num := odx.connect_env('orders.dsn','file=dsn');
 
  -- open a cursor
  cursor_num := odx.open_cursor(instance_num);
 
  -- qualify rows using MDK indexes
  odx.parse_sql(cursor_num, 'qualify customers where company = :kwd');
 
  -- assign the keyword
  odx.set_variable(cursor_num, ':kwd', 'boe*');
 
  -- execute the sql and get the qualifying count
  qual_count := odx.execute_sql(cursor_num);
  DBMS_OUTPUT.put_line('Rows qualified: ' || TO_CHAR(qual_count));
 
  -- show the qualified rows and its child-table rows
  odx.parse_sql(cursor_num, 'select customer_no,company,contact from customers with odxid');
 
  -- set the column types
  odx.set_column_type(cursor_num, 1, customer_num);
  odx.set_column_type(cursor_num, 2, company);
  odx.set_column_type(cursor_num, 3, contact);
 
  -- execute the select sql
  ignore := odx.execute_sql(cursor_num);
 
  -- open a second cursor for child table retrieval
  child_cursor := odx.open_cursor(instance_num);
 
  -- select rows from the child table
  odx.parse_sql(child_cursor, 
    'select customer_no,date_tickler,tickler,initials,action_info from activity where customer_no = :cust_no order by date_tickler desc');
 
  -- define the column list for the child table  
  odx.set_column_type(child_cursor, 1, customer_num);
  odx.set_column_type(child_cursor, 2, date_tickler);
  odx.set_column_type(child_cursor, 3, tickler);
  odx.set_column_type(child_cursor, 4, initials);
  odx.set_column_type(child_cursor, 5, action_info);
 
  -- let's fetch the row from the parent table until the end
  WHILE odx.get_row(cursor_num) > 0 LOOP
 
    -- get the column values from parent table
    odx.get_column(cursor_num, 1, customer_num);
    odx.get_column(cursor_num, 2, company);
    odx.get_column(cursor_num, 3, contact);
 
    -- display it
    counter := odx.get_last_row_count(cursor_num);
    DBMS_OUTPUT.put_line( TO_CHAR( counter) || ') ' || TO_CHAR(customer_num) || ' ' || company || ' ' || contact);
 
    -- let's retrieve rows from the child table for this particular customer number
    odx.set_variable(child_cursor, ':cust_no', customer_num);
    ignore := odx.execute_sql(child_cursor);
 
    -- fetch the child-table rows
    WHILE odx.get_row(child_cursor) > 0 LOOP
 
      -- get the column values from child table
      odx.get_column(child_cursor, 1, customer_num);
      odx.get_column(child_cursor, 2, date_tickler);
      odx.get_column(child_cursor, 3, tickler);
      odx.get_column(child_cursor, 4, initials);
      odx.get_column(child_cursor, 5, action_info);
 
      -- display it
      DBMS_OUTPUT.put_line( ' - ' || TO_CHAR(customer_num) || ' ' || TO_CHAR(date_tickler) || ' ' || tickler ||
        ' ' || initials || ' ' || action_info);
 
    END LOOP;
 
  END LOOP;
 
  -- clean up
  odx.close_cursor(child_cursor);
  odx.close_cursor(cursor_num);
  odx.disconnect_env(instance_num);
 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error occured.');
 
    -- check if we need to close the cursors to avoid memory leak
    IF odx.is_opened(cursor_num) THEN
      odx.close_cursor(cursor_num);
    END IF;
    IF odx.is_opened(child_cursor) THEN
      odx.close_cursor(child_cursor);
    END IF;
 
    -- check if we need to disconnect to avoid memory leak
    IF odx.is_connected(instance_num) THEN
      odx.disconnect_env(instance_num);
    END IF;
 
    -- raise the exception
    RAISE;  
END;
/