This discussion is archived
6 Replies Latest reply: Nov 18, 2012 9:24 PM by JackK RSS

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

JackK Explorer
Currently Being Moderated
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. Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Thanks to all of you!

    Jacek

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points