This content has been marked as final. Show 6 replies
Suppose I'm running SQL*Plus:Do you mean how to pass a variable to the script ? If so, here an example
How 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 184.108.40.206.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 220.127.116.11.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 $
Sorry, there might not be a good, straightforward way to do that. I certainly don't know of one.
Here's a klugey, roundabout way:
The SQL*Plus command
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 ;
Set_foo_to_default.sql might look somehting like this:
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
Depending on how your system encodes linefeeds, you may have to change step (3) a litte. When a line begins with any amout of whitespace, then @, SQL*Plus inserts the contents of the given script at that point. In step (3), I used a string literal that started on one line, and then continued on another. My system (Windows XP) uses a single character for newlines, so that's why I looked for 'SP2-0135' starting at position 2 of the string literal. Some systems use 2 characters for newlines; if yours does that, then change 2 to 3 in SUBSTR.
I would do this only as a last resort. See if your OS has a more elegant way to handle the if-then-else logic before starting SQL*Plus, and then, when you do start SQL*Plus, pass it an argument, as Paul suggested.
Edited by: Frank Kulash on Nov 5, 2012 5:06 PM