I would like to pass parameters to a sqlscript with the &&
so that one variable is defined for the entire script
sqlplus scott/tiger @script foo
I tried everything and read and still it prompts me for the foo
variable... sometimes it does work ( set verify off ) and just
gives me the results... in others how do I pass variables
to a sql script through sqlplus without having sqlplus prompt
me for what the variable is, especially since I pass it.
Just thought I would add this in
You can bypass the prompts for values associated with substitution variables by passing values to parameters in a script through the START command.
You do this by placing an ampersand (&) followed by a numeral in the script in place of a substitution variable. Each time you run this script, START replaces each &1 in the file with the first value (called an argument) after START filename, then replaces each &2 with the second value, and so forth.
For example, you could include the following commands in a script called MYFILE:
SELECT * FROM EMP_DETAILS_VIEW
In the following START command, SQL*Plus would substitute PU_CLERK for &1 and 3100 for &2 in the script MYFILE:
START MYFILE PU_CLERK 3100
Is it possible to do this from the command line with sqlplus
sqlplus scott/tiger @myfile PU_CLERK 3100
the reason why I want to do this is I would like to cursor through
a list of tables in a schema and dynamically generate sql loader control files.
Ugly. Reason: does not use bind variables.
Not using bind variables is said to be the #1 reason for poor performing Oracle applications.
Granted, using bind variables in SQL*Plus from input is difficult as the assignment statement itself needs to be executed by using an anonymous Oracle PL/SQL block - and that will cause duplicate assignment statements in the Shared Pool as these are non-sharable.
SQL> -- define a bind variable
SQL> var salValue number
SQL> -- assign the input value (this results in a non-sharable SQL)
SQL> exec :salValue = &1;
SQL> -- execute the SQL using bind variables, resulting in a sharable SQL
SQL> select * from emp_details where salary = :salValue;
Even so, this approach shows an understanding and appreciation of using bind variables and firing off sharable SQL at Oracle. And in most other languages, the assignment statement can be done by the local language itself, instead of having to use PL/SQL as SQL*Plus has to - which means this approach will result in proper sharable SQL.
The same technique should be followed in SQL*Plus - as this is the correct technique in other languages. Especially since SQL*Plus is often used as testing ground before implementing code in other languages.
-- ==== x.sql ===============def PARAM1=&1def PARAM2=&2select * from tab where tname = '&PARAM1' or cname='&PARAM2' ;-- ========================sqlplus scott/tiger @x.sql EMP DEPT
worked thankyou very much I knew that this was possible but
forgot how to do and looked everywhere... the books went on and
on about prompting for variables but nothing about how to pass
command line arguments silently... thanks again!