Link to home
Create AccountLog in
Avatar of it-rex
it-rex

asked on

CURSOR_SHARING AND PL/SQL (call vs begin)

hello

the book cost based oracle by Jonathan Lewis states:

CURSOR_SHARING AND PL/SQL
In most cases, Oracle will replace literal values with bind variables with names like :SYS_B_0; however, if you
write code that calls anonymous PL/SQL blocks using the old begin proc_name (...)end; syntax, then
Oracle will not perform bind variable substitution. Since PL/SQL blocks don’t need to be optimized (even
though they still need to have cursor areas prepared for them), the overhead caused by this deficiency in the
mechanism may be something you can put up with.
If you can convert your code to the newer call proc_name(...) syntax, then Oracle will do bind variable
substitution. Note, however, that some older versions of Oracle have a bug that causes sessions to crash
if you mix literals and real bind variables in a call, and then enable cursor_sharing.



my Question is:seraching our v$sql we found that all plsql calls made as begin "CALL" is not used at all.

something like
begin appl_test_update(:Var1,:Var2)

is this something that we can fix and replace begin with call?
if so what do we need to verify here?
does this apply to specific PLSQL code?
ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of it-rex
it-rex

ASKER

Is there anything else we need to check inside the plsql code?
Sorry, I don't get it...Can you explain exactly what is it that you want to accomplish?
Avatar of it-rex

ASKER

tnx