6 Replies Latest reply: Nov 18, 2012 11:24 PM by JackK RSS

    Setting a variable in a command (sqlplus ... var=val)

    JackK
      Hi!
      Suppose I'm running SQL*Plus:
      $ sqlplus user/pass@db @script.sql
      How may I set a variable SOME_VAR in that command?
      At the moment I have to set that variable in script.sql using ACCEPT command.

      Thanks in advance...
      Jacek
        • 1. Re: Setting a variable in a command (sqlplus ... var=val)
          Paul M.
          Suppose I'm running SQL*Plus:
          $ sqlplus user/pass@db @script.sql
          How may I set a variable SOME_VAR in that command?
          Do you mean how to pass a variable to the script ? If so, here an example
          $ cat script.sql
          select &1 from dual;
          $ export SOME_VAR=123
          $ sqlplus test/test @script $SOME_VAR
          
          SQL*Plus: Release 11.2.0.3.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 11.2.0.3.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>
          • 2. Re: Setting a variable in a command (sqlplus ... var=val)
            JackK
            How may I check if a variable is set? I'd like to ask for a variable's value only if it's not set yet.
            • 3. Re: Setting a variable in a command (sqlplus ... var=val)
              Paul M.
              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
              
              $
              • 4. Re: Setting a variable in a command (sqlplus ... var=val)
                JackK
                Sorry, I did not say what I mean exact. I mean: how to check in SQL*Plus if a variable is set? If it's not set yet then I will execute ACCEPT command.
                • 5. Re: Setting a variable in a command (sqlplus ... var=val)
                  Frank Kulash
                  Hi,

                  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
                  DEFINE  foo
                  without an = sign means "display the value of &foo". If foo is defined, then the output will be something like
                  DEFINE  FOO          = "123" (CHAR)
                  but if &foo is not defined, you'll get the following error:
                  SP2-0135: symbol foo is UNDEFINED
                  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:
                  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
                  ;
                  In step (4), this script runs another, pre-existing script.
                  Set_foo_to_default.sql might look somehting like this:
                  DEFINE  foo = 10
                  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.

                  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
                  Added example.
                  • 6. Re: Setting a variable in a command (sqlplus ... var=val)
                    JackK
                    Thanks to all of you!

                    Jacek