Suppose I'm running SQL*Plus:Do you mean how to pass a variable to the script ? If so, here an exampleHow may I set a variable SOME_VAR in that command?
$ sqlplus user/pass@db @script.sql
$ cat script.sql select &1 from dual; $ export SOME_VAR=123 $ sqlplus test/test @script $SOME_VAR SQL*Plus: Release 188.8.131.52.0 Production on Mon Oct 29 14:53:00 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options old 1: select &1 from dual new 1: select 123 from dual 123 ---------- 123 SQL>
I'd like to ask for a variable's value only if it's not set yet.Example :
$ cat script.sql set pages 0 veri off select '&1','&2','&3' from dual; exit $ $ cat script.sh if [ ! "$VAR1" ]; then echo -n "Enter value for VAR1 " read VAR1 fi if [ ! "$VAR2" ]; then echo -n "Enter value for VAR2 " read VAR2 fi if [ ! "$VAR3" ]; then echo -n "Enter value for VAR3 " read VAR3 fi sqlplus -s test/test @script $VAR1 $VAR2 $VAR3 $ $ export VAR2=22 $ $ ./script.sh Enter value for VAR1 11 Enter value for VAR3 33 11 22 33 $
without an = sign means "display the value of &foo". If foo is defined, then the output will be something like
but if &foo is not defined, you'll get the following error:
DEFINE FOO = "123" (CHAR)
So you could SPOOL the results of "DEFINE foo", then examine the contents of the output file to see if it starts with 'SP2-0135', and branch accordingly. For example, say we want to set foo = 10 if foo is not already defined. If foo is already defined, we don't want to change it. The following script does that:
SP2-0135: symbol foo is UNDEFINED
In step (4), this script runs another, pre-existing script.
PROMPT ***** (1) Set foo (or make sure it's undefined) for testing purposes UNDEFINE foo DEFINE foo = 20 -- The last command above will override the other one(s) -- Re-arrange them as needed for testing PROMPT ***** (2) Create what_is_foo.txt SPOOL c:\temp\what_is_foo.txt DEFINE foo SPOOL OFF PROMPT ***** (3) Set NEXT_SCRIPT based on what_is_foo.txt COLUMN next_script_col NEW_VALUE next_script SELECT CASE SUBSTR ( ' @c:\temp\what_is_foo.txt ' , 2 -- or maybe 3 on your system , 8 ) WHEN 'SP2-0135' THEN 'set_foo_to_default' ELSE 'foo_is_already_set' END AS next_script_col FROM dual; PROMPT ***** (4) Run NEXT_SCRIPT -- next_script will be either SET_FOO_TO_DEFAULT.SQL -- or FOO_IS_ALREADY_SET.SQL @@&next_script PROMPT ***** (5) FOO is now ready to use SELECT deptno , ename FROM scott.emp WHERE deptno = &foo ;
The other sub-script, foo_is_already_set.sql can consist entirely of comments, or it can even be empty, but you must have a script called foo_is_already_set.sql.
DEFINE foo = 10