• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Get ResultSet by executing anonymous pl/sql block.

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..?

Thanks,
Raj
 
Bartender
Posts: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

...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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic