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