1

I want to write pl/sql code which utilizes a Cursor and Bulk Collect to retrieve my data. My database has rows in the order of millions, and sometimes I have to query it to fetch nearly all records on client's request. I do the querying and subsequent processing in batches, so as to not congest the server and show incremental progress to the client. I have seen that digging down for later batches takes considerably more time, which is why I am trying to do it by way of cursor.

Here is what should be simple pl/sql around my main sql query:

declare
  cursor device_row_cur
    is
      select /my_query_details/;

  type  l_device_rows is table of device_row_cur%rowtype;
  out_entries l_device_rows := l_device_rows();

begin
    open device_row_cur;    
      fetch device_row_cur
      bulk collect into out_entries
      limit 100;        
  close device_row_cur; 
end;

I am doing batches of 100, and fetching them into out_entries. The problem is that this block compiles and executes just fine, but doesn't return the data rows it fetched. I would like it to return those rows just the way a select would. How can this be achieved? Any ideas?

shasan
  • 178
  • 2
  • 13

2 Answers2

3

An anonymous block can't return anything. You can assign values to a bind variable, including a collection type or ref cursor, inside the block. But the collection would have to be defined, as well as declared, outside the block. That is, it would have to be a type you can use in plain SQL, not something defined in PL/SQL. At the moment you're using a PL/SQL type that is defined within the block, and a variable that is declared within the block too - so it's out of scope to the client, and wouldn't be a valid type outside it either. (It also doesn't need to be initialised, but that's a minor issue).

Dpending on how it will really be consumed, one option is to use a ref cursor, and you can declare and display that through SQL*Plus or SQL Developer with the variable and print commands. For example:

variable rc sys_refcursor

begin
  open :rc for ( select ... /* your cursor statement */ );
end;
/

print rc

You can do something similar from a client application, e.g. have a function returning a ref cursor or a procedure with an out parameter that is a ref cursor, and bind that from the application. Then iterate over the ref cursor as a result set. But the details depend on the language your application is using.

Another option is to have a pipelined function that returns a table type - again defined at SQL level (with create type) not in PL/SQL - which might consume fewer resources than a collection that's returned in one go.

But I'd have to question why you're doing this. You said "digging down for later batches takes considerably more time", which sounds like you're using a paging mechanism in your query, generating a row number and then picking out a range of 100 within that. If your client/application wants to get all the rows then it would be simpler to have a single query execution but fetch the result set in batches.

Unfortunately without any information about the application this is just speculation...

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thank you, you make some very good points. My front end application is in Java. And you're very right in guessing that I have a row-number based paging going on in that code. As I had mentioned before the rationale behind querying in chunks was not to overload the server, which might sometimes have to process a number of such large requests at a time. I would think if I query all data just once, it will all be kept in memory on server until consumed and 'freed' from the client. Is that a wrong assumption? – shasan Sep 27 '14 at 00:21
  • Also, what java.sql api were you referring to when you mentioned 1) iterating over the returned cursor as a result set, 2) fetching the result set in batches. I'm assuming you were thinking in java based on the nomenclature you used. Thank you for your comments! – shasan Sep 27 '14 at 00:22
1

I studied this excellent paper on optimizing pagination: http://www.inf.unideb.hu/~gabora/pagination/article/Gabor_Andras_pagination_article.pdf

I used technique 6 mainly. It describes how to limit query to fetch page x and onward. For added improvement, you can limit it further to fetch page x alone. If used right, it can bring a performance improvement by a factor of 1000.

Instead of returning custom table rows (which is very hard, if not impossible to interface with Java), I eneded up opening a sys_refcursor in my pl/sql which can be interfaced such as:

OracleCallableStatement stmt = (OracleCallableStatement) connection.prepareCall(sql); stmt.registerOutParameter(someIndex, OracleTypes.CURSOR); stmt.execute(); resultSet = stmt.getCursor(idx);

shasan
  • 178
  • 2
  • 13
  • I see I didn't ever reply to your comment on my answer; yes, this is exactly what I meant about the returned cursor as a result set. This is the Java equivalent of the PL/SQL version I used. – Alex Poole Oct 24 '14 at 19:39