• 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 ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Junilu Lacar
  • Rob Spoor
  • Liutauras Vilda
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Piet Souris
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Himai Minh
  • Carey Brown
  • Frits Walraven

prepared statements in PL/SQL

Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a statement in a PL/SQL that is executed repeatedly in a loop. It is as follows
select last_name from employees where department_id = epartment_id

The only change in each loop is the bind variable �departement_id�. For each loop ORACLE has to perform the following steps for the execution of the statement:
1, Open Cursor
2, Parse
3, Define Column
4, Bind Variable
5, Execute
6, Fetch Rows
7, Close cursor
Now, there is no need to execute steps 1, 2,3 and 7 every time my program makes a loop thus they should be outside of the loop. The only steps inside of the loop should be 4,5,6. The idea is similar to prepared statements in JDBC, but I want to apply it to a PL/SQL function. Also would explicit cursors solve this problem? If not, is there a special build-in library that can help me? (I have seen some PL/SQL packages that addressed this issue but all of the solutions were too complex. I think that this is such a trivial and common problem that ORACLE should already develop some solution. I am just trying to avoid �reinventing the wheel�)
Posts: 17346
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually since you used bind variables, the statement will only be hard parsed once, each query after will use the query that is in the SGA area.
Open cursor is like a call to runt he query, so each time you will have to do this. No you can use the loop mechanism.

This was taken from this site.

You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    Bookmark Topic Watch Topic
  • New Topic