bind variables and sql*plus

Should binds be used instead of substitution variables in SQL*Plus?

I'm well aware of the potential disasters of not using bind variables. We have plans to run some production processes through SQL*Plus (for simplicity). These scripts may be called thousands of times a day with a wide variety of command line parameters. The direct way to incorporate these might be to just use the substitution variables in the sql queries and any anonymous pl/sql blocks (e.g. '&&1'). Of course this means another copy of that queries cursor or pl/sql block for every unique substitution value. My thought was to require the use of actual bind variables in all queries and blocks. Then assign the substitutions to the actual bind variables at the beginning of the script. For example ...

variable my_bind varchar2(30)
exec :my_bind := '&&1'

select ...
where ... = :my_bind

This still requires a unique cursor for every substitution value but the cursor is basically just that a bunch of individual pl/sql assignment statements. My rationale was that this would reduce the amount of wasted resources. Also, these small statements should fit easily into any holes created by fragmentation.

Does my recommendation make sense?
Is there a better way to handle this?

Thanks,
Richard Received on Mon Feb 24 2003 - 13:09:10 CST