Thanks for the question, Brandon.
Asked: April 21, 2010 - 5:44 pm UTC
Last updated: April 22, 2010 - 9:33 am UTC
Version: 10.2.0.4
Viewed 1000+ times
You Asked
Hi Tom,
I saw your note about no Forms/Reports questions, but this is a borderline Reports/Database questions so I'm hoping you may know the answer.
I've noticed recently in my database that queries coming from a 3rd party Oracle Forms/Reports application are using bind variables, but the variables aren't being peeked. When I run a 10053 trace on the queries, I see the message "No bind buffers allocated" for the bind variables. I've opened an SR and was told that this is normal for Oracle Reports - bind variable values aren't provided at parse time, and therefore can't be peeked, however I was shocked to find that an Oracle application would operate in this way and also that it isn't documented anywhere. I searched all over the Internet, Oracle Docs and My Oracle Support and couldn't find a single piece of documentation, blog entry or anything from anyone noting this behavior in Oracle Reports so I'm just looking for confirmation. Have you ever noticed this before? And, are you aware of any way to modify the default behavior so that Oracle Reports will allow bind variable peeking?
Thanks!
Brandon
and Tom said...
Sorry, I've never actually programmed using reports - ever.
Bind peeking isn't something the tool needs to do, it is something that happens in the client automatically. Since 9i we've used what is called a deferred parse - when you "parse" a sql statement - we go through all of the steps (syntax, semantic check, shared pool search) and if we find out this is a hard parse - we DEFER the rest of the processing until later (so the parse returns, it is not done yet but it returns)
the client then binds
and when the client opens (executes, whatever you want to call it - goes to get the first row) the query, we hard parse THEN - after we have the binds.
No code ever need be changed to have bind peeking happen, it just happens
ASSUMING the client libraries support it. So, probably you have an really really old version of forms that is using old client libraries that didn't do bind peeking (this to me would be the most likely).
Today, in 2010, I'm not aware of any client library that doesn't support it - all of the way from jdbc thin (pure java) to OCI.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment