I have been making some pretty massive improvements to our software lately by migrating the Pro*C that used static SQL over to dynamic SQL and dynamic binds. I've so far improved our soft parses by 25% or more, which is about as best as it'll get since we allow the user to dynamically change sort columns and the like which I can't bind and will force new queries.
Anyway, I'm now trying to decide what more optimizations I can do, and one in particular that struck me is reducing the amount of PREPARE statements done by the software. Basically, our software sits out there and constantly (every 10 seconds or so) queries Oracle to refresh itself. Most of the time the query is 100% indentical, so all I do now is the following:
PREPARE query FROM sql;
DECLARE cursor CURSOR FOR query;
DESCRIBE BIND VARIABLES FOR query INTO bindDescriptor;
... (creating bind descriptor, etc.) ...
OPEN cursor USING DESCRIPTOR bindDescriptor;
FETCH (using host arrays)
Now that loop runs every 10 seconds, but notice there is never a close to the cursor. Now, Oracle documentation tells me that this is OK since I can re-open a cursor (with the same or new binds) without issue. However, when the if body gets executed, I'm worried that the PREPARE and following DECLARE (on an already open cursor) may muck things up. However, I can't do a close before the declare because Pro*C doesn't yet know about the cursor.
I guess I'm hoping that the Pro*C interface is smart enough to realize (upon DECLARE) that cursor is already open and being redeclared and needs to be closed first. I don't want any issues arising from never closing a cursor.