• 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
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

prepared statements in PL/SQL

 
Greenhorn
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
LOOP
select last_name from employees where department_id = epartment_id
END LOOP

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�)
 
ranger
Posts: 17347
11
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.

Mark
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic