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;
/
