Omnidex's PL/SQL Interface

Example

set serveroutput on

declare

begin

dbms_output.enable();

-- connect to the Omnidex Data source file

instance_num := odx.connect_env('orders.dsn','file=dsn,networkservices=odxsrvr');

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

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

-- retrieve the 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 free up resources

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 free up resources

if odx.is_connected(instance_num) then

odx.disconnect_env(instance_num);

end if;

-- raise the exception

raise;

end;

/

-- end of PL/SQL Procedure Example

top