Sqlplus parameterized query via file

oracleoracle-10gsqlplus

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a person to be prompted for and we would like to eliminate user error when entering the parameter for this.

The reason we need to issue a parameterized query is that we are seeing a huge difference in performance between a straight sql statement and the same query issued as a parameterized query in our application. We would like to remove any application code from the chain and have the difference evident with only Oracle tools.

Best Answer

If you want the SQL in one file and the parameters in another, this is an option. Have query.sql assigning positional parameters to bind variables and then executing a query:

variable p_owner varchar2(30);
variable p_column varchar2(30);

set verify off
set feedback off

begin
    :p_owner := '&1';
    :P_column := '&2';
end;
/

set feedback on

select table_name
from all_tab_columns
where owner = :p_owner
and column_name = :p_column;

exit 0;

And put the parameter values in a text file, say parms.txt:

SYS
OSUSER

And put it together with:

sqlplus -s user/password @query `cat parms.txt`

(This is for Unix, obviously; not sure off-hand what the command-line equivalent is for Windows). Change parms.txt, or use different files, and you'll still get the same hard-parsed plan each time.

Your performance problem sounds like it might possibly be related to bind variable peeking, particularly the 'stuck with a bad plan' description. The execution plan is determined by the first query run, which might choose suitable indexes etc. for those parameters; subsequent runs then use the same plan even if different indexes might be more suitable for the new parameters. The stand-alone straight SQL will have a separate parse and thus possibly different (more suitable) plan. In the past I've had to gather stats manually to prevent histograms being used, which at least stabilises things.

Related Question