Skip to Main Content
  • Questions
  • Oracle Reports not supplying bind variables

Breadcrumb

Easter

Question and Answer

Tom Kyte

Thanks for the question, Brandon.

Asked: April 21, 2010 - 5:44 pm UTC

Answered by: Tom Kyte - Last updated: April 22, 2010 - 9:33 am UTC

Category: Database - 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 we 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.



and you rated our response

  (1 rating)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

Reports Version 10.1.2.3

April 22, 2010 - 11:37 am UTC

Reviewer: A reader

Sorry, I should've specified the Oracle Reports version in my initial post - it is 10.1.2.3. I suspect maybe even this recent version of Reports still doesn't support the deferred parse. The bevavior seems similar to what happens with dbms_sql as noted in another of your posts here: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2444907911913#55014877907946

I created a simple test report to reproduce the problem and Oracle Support reproduced it on their internal system so it's not just a configuration problem on my end.

I also see the same behavior, but intermittently, with queries coming from Oracle Forms 10.1.2.3 in the same environment - some queries have bind variables bound and peeked, and others just report the "No bind buffers allocated" message. I haven't been able to determine what causes some to be bound, but not others. I've never done any Forms or Reports development either so I'm not very strong at analyzing the code to find the difference - I'm a DBA so I'm just trying to figure out why bind variable peeking isn't happening, which sometimes causes bad execution plans.

Thanks,
Brandon