Re: Oracle Object Types and Java (migrating from mod_plsql)

From: Thanatos <thanatic_at_telstra.com>
Date: 21 Feb 2005 14:50:29 -0800
Message-ID: <1109026229.105553.231540@z14g2000cwz.googlegroups.com>

Is your application using (displaying) all 2000 rows at once?

I guess not as you talk about pagination. A typical pagination query looks something like:

select * from
( select a.*, rownum rnum

   from (

	select /*+ FIRST_ROWS */ *
	from '||table_name||'
	where '||bound_where||'
	order by '||order_by||'

) a
 where rownum <= '||(max_page_rec*page_number) ||' )
where rnum > '||(max_page_rec*(page_number-1));

This will get you records N to M depending on records per page
(max_page_rec) and the current page number (page_number.)

A couple of questions:
I guess I should bind (max_page_rec*page_number) and
(max_page_rec*(page_number-1)) ?

Also given the above query:

I open the cursor via a procedure call:

open_bound_cursor(sql_statement,search_param.bind_array,vehicle_cur);

Which binds all values for the query and returns a cursor (vehicle_cur) - it's an "out" parameter.

--open_bound_cursor
procedure open_bound_cursor(

sql_statement	in		varchar2,
bind_array		in		web_arr,
bound_cur		in out	CurType)

is

        bind_count number := bind_array.count;

begin

	if bind_count = 0 then
		open bound_cur for sql_statement;
	elsif bind_count = 1 then
		open bound_cur for sql_statement
		using	bind_array(1);
	elsif bind_count = 2 then
		open bound_cur for sql_statement
		using	bind_array(1),bind_array(2);
	elsif bind_count = 3 then
		open bound_cur for sql_statement
		using	bind_array(1),bind_array(2),bind_array(3);
	elsif bind_count = 4 then
		open bound_cur for sql_statement
		using	bind_array(1),bind_array(2),bind_array(3),bind_array(4);
	elsif bind_count = 5 then
		open bound_cur for sql_statement
		using	bind_array(1),bind_array(2),bind_array(3),bind_array(4),bind_array(5);

/* ETC ETC ETC */         end if;

end open_bound_cursor;

I then process each row:

/*

type CurType			is ref cursor;
vehicle_cur				CurType;

*/

open_bound_cursor(sql_statement,search_param.bind_array,vehicle_cur);

loop
  fetch vehicle_cur into vehicle_rec;
  exit when vehicle_cur%NOTFOUND;

  --do something

end loop;
close vehicle_cur;
end;

This represents the most intensive/used sql on our system.

Should I be doing it differently? I have read about dbms_session.set_context and whilst it maybe more elegant, is perhapse the "if" tests on opening the cursor depending on the bind count faster?

Also given that I have to build the sql_statement based on the search criteria, native sql is out of the question ie there is no way to code something like:

OPEN p_refcur FOR select * from my_table where blah;  

So I am stuck with dynamic sql for opening the cursor.

Regards Received on Mon Feb 21 2005 - 16:50:29 CST