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�)