2 Replies Latest reply on Feb 26, 2014 5:23 PM by user10863704

    Bug: Using substitution variables as values for SET commands in script

    user10863704

      When running a saved script via the @ command in SQL Developer (e.g. @ScriptName), or when running a script that has been loaded into the UI, SET commands that use bind variables as values are not working properly.

       

      The issue is that a command such as the following works as expected when &value = 'ON', but seems to have no effect when &value = 'OFF':

      SET TIMING &value

       

      For example, the following can be used to set TIMING to ON if it is currently OFF (SHOW command returns "timing ON", as expected):

      SET TERMOUT OFF

      COL t1_temp NEW_VALUE t1

      SELECT 'ON' t1_temp FROM dual;

      SET TIMING &t1

      SET TERMOUT ON

      SHOW TIMING

       

      However, if TIMING is currently ON, the following has no effect (SHOW command returns "timing ON" instead of "timing OFF"):

      SET TERMOUT OFF

      COL t1_temp NEW_VALUE t1

      SELECT 'OFF' t1_temp FROM dual;

      SET TIMING &t1

      SET TERMOUT ON

      SHOW TIMING

       

      In SQL*Plus, both of the above examples can be used without issue to change the value of TIMING.

        • 1. Re: Bug: Using substitution variables as values for SET commands in script
          Gary Graham-Oracle

          Interesting.  The basic SET TIMING ON / SET TIMING OFF commands work fine, using literal ON/OFF values, without substitution.

           

          But even a trivial script using substitution,  something like

          ACCEPT VAL 'Enter ON or OFF'

          SET TIMING &VAL.

          SHOW TIMING

          suffers the same problem as your COL ... NEW_VALUE case.  As for COL ... NEW_VALUE, it works fine from the worksheet in the following example I've borrowed...

          create table new_value_test_from (

            some_field_name Varchar2(20)

          );

           

          create table new_value_test_to (

            another_field Varchar2(20)

          );

           

          insert into new_value_test_from values ('SQL Developer 4.0.1');

           

          column some_field_name new_value some_var_name

           

          select some_field_name from new_value_test_from;

           

          insert into new_value_test_to values ('&some_var_name');

           

          select * from new_value_test_to;

          I'll log a bug -- thanks for taking the time to report this.

           

          Regards,

          Gary

          SQL Developer Team

           

          Message was edited by: Gary Graham Bug 18309409 - FORUM: VARIABLE SUBSTITUTION NOT WORKING WITH SET TIMING SUBCOMMAND

          • 2. Re: Bug: Using substitution variables as values for SET commands in script
            user10863704

            Hi Gary.

             

            Thanks for the response.

             

            FYI, the issue is not limited to the TIMING subcommand.  It is also present when using various other subcommands, e.g. ECHO, FEEDBACK, TERMOUT, VERIFY.

             

            However, some subcommands do not have the issue, e.g. ESCAPE, HEADING.

             

            -Michael