File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Get ResultSet by executing anonymous pl/sql block. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Get ResultSet by executing anonymous pl/sql block." Watch "Get ResultSet by executing anonymous pl/sql block." New topic

Get ResultSet by executing anonymous pl/sql block.

Rajukpo kumar

Joined: Dec 31, 2010
Posts: 9
Hi all,

We are using Oracle with PL/SQL.
I am using an anonymous blocks to get the data. I need to run about four different select queries and be able to navigate through it. It will have to have loops within loops. I did a similar thing in Java and i just assigned all the results to a Vector (or I suppose an array could also have worked), iterate through it with a loop (iterate through a different resultset within this loop) and then do some checks.

QUESTION: How do I navigate through the results (in SQL) after I ran the query? I read somewhere about cursors, but is this the only way? I see that all don't recommend it. If I should use a cursor, how does it work. What is the syntax, etc?

Any help..?

Vijitha Kumara

Joined: Mar 24, 2008
Posts: 3902

...I did a similar thing in Java and i just assigned all the results to a Vector....

How you've done this? Can you show us the code?
Note that the Vector is synchronized unlike other collection implementations.

[How to ask questions] [Twitter]
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

If you want to program in PL/SQL, you should read the documentation. If you still have problems, come back here to ask specific questions. For handling cursors, see chapter 6 - Static SQL. (I've given link to most recent documentation, if you have a different version of Oracle, find corresponding documentation.)

Cursors are the standard way to handle data in PL/SQL, they cannot be avoided, and are used even by JDBC to retrieve data from the database. Thus the recommendation to not use them does not make sense. (The only way to avoid cursors is to manipulate data using SQL - see the next point).

However, you should reconsider whether the thing you want to program in PL/SQL is not doable using plain SQL. Oracle can efficiently execute even very complicated SQL statements with lots of joined tables, and it will always be much more performant than any PL/SQL construct. Even merging some of the four selects together and eg. processing two instead of four would be benefical. "Loops of loops" type of operation sounds like an operation that would be very good candidate to be performed in SQL. Complicated updates of joined tables are also possible.

If you do it in PL/SQL and are not on 11g, definitely use bulk collect - a kind of batching. In 11g, this optimization si done automatically behind the scenes.

And finally, if you do need PL/SQL, consider using stored procedure (or better - package). Anonymous blocks get compiled anew with every execution, while stored procedures are compiled once (upon creation) and then reused.

Edit: It should be possible to return an array from PL/SQL using collections. You might also create a function that returns a ref cursor. I'd suggest you to consider using pure SQL first, then you won't have to research into returning collections from PL/SQL block. It should definitely be doable, but I don't have any particular experience with it.
chris webster

Joined: Mar 01, 2009
Posts: 2290

See Martin's reply above. Cursors (which return result sets) are how you use SQL in procedural contexts such as Java or PL/SQL, although as Martin says, you may be able to do everything in one big SQL statement which could make things easier.

As for looping within loops, think about what you are trying to do in terms of sets of data, not individual rows. Many 3GL developers have problems getting out of the procedural row-by-row mentality, but SQL is designed to perform bulk manipulations on sets of data (rows/colums) and provides powerful tools to do this e.g. joins, WHERE filters, sub-SELECTs, combinations of UNION/INTERSECT/MINUS, top-N queries etc. If you can re-define your problem in these terms (which is very often possible) you will find that SQL is a far more powerful and efficient way to solve it.

Once again, if you're planning to do any serious work with an RDBMS, you really need to learn how to use these tools properly.

No more Blub for me, thank you, Vicar.
I agree. Here's the link:
subject: Get ResultSet by executing anonymous pl/sql block.
It's not a secret anymore!