4 Replies Latest reply: Feb 19, 2013 3:33 PM by Frank Kulash RSS

    SQLPlus not prompting for substitution variable when script is run

    9423755
      Hello gurus,

      In line 9 of pln.sql below, there is a substitution variable "&1".
      The first time I ran the query pln.sql, it prompted me for a value for &1.
      On all subsequent executions of pln.sql, it just uses the value of &1. It doesn't prompt me for a value.
      According to http://www.orafaq.com/wiki/SQL*Plus_FAQ,

      >
      "&" is used to create a temporary substitution variable that will prompt you for a value every time it is referenced.
      >

      So, what am I missing? Does the behaviour of variable substitution change when the variable in inside a script?
      Code listing is below; beneath the listing you can see "def 1" shows that the variable &1 already has the string "Oracle" assigned to it.
      I would like to be able to run this script and each time have it prompt me for a value for &1.

      Many thanks,
      Jason

      HR@XE> get C:\Users\J\Documents\SQL\ProOracleSQL\chapter05_scripts\pln.sql
        1  SELECT xplan.*
        2  FROM
        3     (
        4     select max(sql_id) keep
        5            (dense_rank last order by last_active_time) sql_id
        6          , max(child_number) keep
        7            (dense_rank last order by last_active_time) child_number
        8       from v$sql
        9      where upper(sql_text) like '%&1%'
       10        and upper(sql_text) not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %'
       11      ) sqlinfo,
       12*    table(DBMS_XPLAN.DISPLAY_CURSOR(sqlinfo.sql_id, sqlinfo.child_number, 'ALLSTATS LAST')) xplan
       13
      HR@XE> def 1
      DEFINE 1               = "Oracle" (CHAR)
      HR@XE> @C:\Users\J\Documents\SQL\ProOracleSQL\chapter05_scripts\pln.sql
      old   9:         where upper(sql_text) like '%&1%'
      new   9:         where upper(sql_text) like '%Oracle%'
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------
      SQL_ID  6k3tn5dr0k17v, child number 0
      -------------------------------------
      SELECT xplan.* FROM  (  select max(sql_id) keep         (dense_rank
      last order by last_active_time) sql_id       , max(child_number) keep
            (dense_rank last order by last_active_time) child_number    from
      v$sql   where upper(sql_text) like '%Oracle%'     and upper(sql_text)
      not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %'   ) sqlinfo,
      table(DBMS_XPLAN.DISPLAY_CURSOR(sqlinfo.sql_id, sqlinfo.child_number,
      'ALLSTATS LAST')) xplan
      
      Plan hash value: 2794990177
      
      ------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
      ------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                   |                   |      1 |        |     13 |00:00:00.05 |       4 |
      |   1 |  NESTED LOOPS                      |                   |      1 |   8168 |     13 |00:00:00.05 |       4 |
      |   2 |   VIEW                             |                   |      1 |      1 |      1 |00:00:00.01 |       0 |
      |   3 |    SORT AGGREGATE                  |                   |      1 |      1 |      1 |00:00:00.01 |       0 |
      |*  4 |     FIXED TABLE FULL               | X$KGLCURSOR_CHILD |      1 |      1 |      0 |00:00:00.01 |       0 |
      |   5 |   COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR    |      1 |   8168 |     13 |00:00:00.04 |       4 |
      ------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - filter((UPPER("KGLNAOBJ") LIKE '%Oracle%' AND UPPER("KGLNAOBJ") NOT LIKE '%FROM V$SQL WHERE
                    UPPER(SQL_TEXT) LIKE %' AND "INST_ID"=USERENV('INSTANCE')))
      
      
      29 rows selected.
        • 1. Re: SQLPlus not prompting for substitution variable when script is run
          Solomon Yakobson
          Supplying single & substitution variable value at a prompt doesn't define it. That is why SQL*Plus will keep prompting for a value each time you reference substitution variable. But as soon as you explicitly define it using define command or by using && value persists and you are not prompted anymore.

          SY.
          • 2. Re: SQLPlus not prompting for substitution variable when script is run
            Frank Kulash
            Hi, Jason,
            942375 wrote:
            Hello gurus,

            In line 9 of pln.sql below, there is a substitution variable "&1".
            The first time I ran the query pln.sql, it prompted me for a value for &1.
            On all subsequent executions of pln.sql, it just uses the value of &1. It doesn't prompt me for a value.
            According to http://www.orafaq.com/wiki/SQL*Plus_FAQ,

            >
            "&" is used to create a temporary substitution variable that will prompt you for a value every time it is referenced.
            >
            That's one way a single & can be used.
            If &1 has already been assigned a permanent value, either by passing a value in a @ or START command, or using "DEFINE 1 = XXX", then SQL*Plus will use that existing value, and not prompt you.
            So, what am I missing?
            If you want to make sure that &1 does not have a value, and therefore you will be prompted for it, you can use this SQL*Plus command:
            UNDEFINE  1
            Personally, I prefer to use the SQL*Plus ACCEPT command, for example:
            ACCEPT  min_order_num  PROMPT "Enter the miminum order_num to be included in the report(e.g.  A0123): "
            This will assign a value to &min_order_num (a much more intuitive name than &1) whether or not one has been assigned before, and you can give a really detailed and helpful prompt to the user.
            • 3. Re: SQLPlus not prompting for substitution variable when script is run
              9423755
              Hi Solomon, Frank

              Thanks for your quick help.
              Reading your replies and doing some quick tests, I've discovered that

              1. Somehow "1" got defined with "&&1" (I've no idea how but then I also don't remember entering the value "Oracle" for "&1")
              2. "&&1" seems to have shadowed "&1" such that when the code saw "&1" it substituted the value for "&&1"
              3. When you do a DEF (or DEFINE) it shows those sub vars defined using && not &. A sub var defined using & doesn't show up in DEF.


              Many thanks again :-)
              Jason

              Edited by: 942375 on 19-Feb-2013 13:00 (typo)
              • 4. Re: SQLPlus not prompting for substitution variable when script is run
                Frank Kulash
                Hi, Jason,
                942375 wrote:
                Hi Solomon, Frank

                Thanks for your quick help.
                Reading your replies and doing some quick tests, I've discovered that

                1. Somehow "1" got defined with "&&1" (I've no idea how but then I also don't remember entering the value "Oracle" for "&1")
                Right. The difference between &1 and &&1 is that &1 (with only 1 &) means "don't remember the value", but &&1 (with 2 &s) means "remember the value".
                When you use @ or START to run a script, the variables &1, &2, ..., &9 will be set to the 1st, 2nd, ..., 9th values you pass after the script name, if you pass them. For example, calling a script like this
                @my_script  "Hello, world"  987
                gives the permanent values 'Hello, world' to &1, and 987 to &2. Since only two arguments were passed, that call does not change &3, &4, ..., &9. If they had values, they continue to have those same values. If they did not have values, they still don't have any.
                2. "&&1" seems to have shadowed "&1" such that when the code saw "&1" it substituted the value for "&&1"
                They are the same variable; the only difference is that, if it has to prompt you for the value, whether it keeps that value permanently or not.
                3. When you do a DEF (or DEFINE) it shows those sub vars defined using && not &. A sub var defined using & doesn't show up in DEF.
                More precisely, when you issue the SQL*Plus command DEF (or DEFINE), you see all the substitution variables that currently have permanent values, regardless of how they were defined. Using a variable name with a single & (for example, &1) may cause SQL*Plus to prompt you for a value, but that value will not be remembered. Once that section of code is parsed, the value you gave will be forgotten.