Sqlplus parameterized query via file

Question

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.

Answer 1

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.

Answer 2

Well, a parameterized query is going to do better, hands down. The way we used to do something similar to this was by using an anonymous PL/SQL block that would use UTLFILE to open and read the parameters, feeding them to query in the block, and writing the output back to the file system.

YMMV of course.

• Darryl Braaten: Actually the issue we have is the paramertized query gets stuck with a bad plan and performs poorly. The DBA will test it with a plain sql statement and report nothing is wrong. For some some small queries doing parameters manually is ok for some we need a better way to provide them to the dba. Do you have an example that uses UTLFILE do read in parameters?
• Leigh Riffel: For 11g see: download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/….
• Leigh Riffel: 10g: download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/…

Answer 3

Since you mention SQL Plus, I assume you're doing this at the command line? If so, this might work for you:

parms.shl:
export parm1=1
export parm2=15
export parm3=150
dojob.shl:
#!/bin/sh
# source our variables into our context.
. parms.shl 
# call sqlplus and have the variables embedded.
sqlplus -s <<EOF
-- we're now in SQLPLUS
SELECT '$parm1', '$parm2', '$parm3'
 FROM DUAL;
-- and exit out
EXIT;
EOF

Then, at the command prompt, run:

./dojob.shl

And the results should match the incoming parameters. The catch is that the SQL needs to be in the shell script. (Pretty sure that using the @filename.sql feature of sqlplus will not see the OS-level environment variables.)

Not the most elegant, but it should work without UTF_FILE overhead/risks. It should work on most *NIX/BSD variants, but it shouldn't be hard to convert to a Windows batch file.

Side Note: Why not store the parameters in a place that's already ready made for storing data? That is, your database? If the parameters need to be changeable, then write a screen or other process that lets the parameters be updated by the end-user, and just link to the table in your process.

Side Side Note: In my example, I am not doing anything with regards to binding variables. Therefore performance is not likely to be great when used in a long-running statement. You can use sqlplus's variables to mitigate the effect (setting them to the OS variable via VARIABLE, and then re-using sqlplus's variables in your actual code. Then Oracle should be able to optimize the statement nicely. See http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html )

Answer 4

Define named parameters owner and table in file params.sql:

 define owner=scott
 define table=emp

Call the definition file and use the parameters in file query.sql

 @params.sql
 select count(*) from &owner..&table
 /
 exit

The .. (two dots) after &owner are intended.

Now change to the directory where params.sql and query.sql are and call

 sqlplus user/password@database @query.sql

The named parameters can be used in the file query.sql in the same way as command line parameters of SQLPlus. The file params.sql can contain other statements than define. There are a lot of technics that can be used with SQLPlus that are described in the SQL*plus manual.