Passing bind variables from Access and VB

[supermega]

Can someone explain(or point me to a page that explains) how the best way to pass bind variables from Access and VB to Oracle. When looking thru my sql statements in the sga i am finding SQL that looks like it is using a bind variable but is parsed 200,000 times and executed 200,000 times, so it doesnt seem to be using it correctly. this is the sql from the sga

SELECT "SUBMISSION_KEY" ,"DIARY_ENTRY" FROM "PRODUCTION"."CLM_CLAIM_DIARY" WHERE ("SUBMISSION_KEY" = :1 )


thanks in advance,
SM

[slimdave]

i wonder if this is a case where the init parameter cursor_sharing might be set to "forced".

I know there's a whole bunch of caveats on the use of this parameter, but you might just try ..


"ALTER SYSTEM SET CURSOR_SHARING=FORCED"

... and see if anything good happens.

[adewri]

Originally posted by slimdave
i wonder if this is a case where the init parameter cursor_sharing might be set to "forced".

I know there's a whole bunch of caveats on the use of this parameter, but you might just try ..


"ALTER SYSTEM SET CURSOR_SHARING=FORCED"

... and see if anything good happens.

;)

its FORCE not FORCED.

"ALTER SYSTEM SET CURSOR_SHARING=FORCE"

I assume there were two typo here ;)

[slimdave]

ah yes. i'm more of a concept person than an implementer.

[supermega]

If i remember correctly that can only be done in 8i release 2, I am on 8.0.5

[slimdave]

Ah, the perfect excuse for an upgrade.

[supermega]

yeah, i agree, great reason for an upgrade...but its not gonna happen :( anytime soon. So I need to figure out how to use bind variables from clients in VB and access.

SM

[slimdave]

Have a look at metalink note 111845.1 "Using Bind variables with RDO, ODBC in SQL Statements", and also try a search on metalink -- i believe they have some odbc/bind variable examples.