5 Replies Latest reply on Mar 7, 2016 3:55 PM by thatJeffSmith-Oracle

    SQLcl: set sqlformat csv breaks returning into substitution variable

    jmarton

      My environment:

      • sqlcl-4.2.0.15.177.0246
      • java: java version "1.7.0_79"
      • linux
      • Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, on linux

       

      The way to define a substitution variable from a SQL expression works using "column foo new_value substvar", then selecting an expression with alias foo. But if I change sqlformat to csv, this function breaks.

       

      See the following script (attached as defvar.sql):

       

      column val new_value mydef
      select 'sample' as val from dual;
      
      select '&mydef.' from dual;
      
      set sqlformat csv
      
      column otherval new_value myotherdef
      select 'second sample' as otherval from dual;
      
      select '&myotherdef.' from dual;
      
      

       

       

      Running the attached defvar.sql script from SQLcl, it will prompt to Enter value for myotherdef. Commenting out "set sqlformat csv" on line 6, it does not prompt for a value for myotherdef, but assigns 'second sample', which is the expected behaviour.

        • 1. Re: SQLcl: set sqlformat csv breaks returning into substitution variable
          Gary Graham-Oracle

          Thanks for taking the time to report this behavior.  It still reproduces in the most recent SQLcl code.  Bug logged.

          • 2. Re: SQLcl: set sqlformat csv breaks returning into substitution variable
            jmarton

            Still reproduces on the most recent available SQLcl version:

            • sqlcl-4.2.0.15.349.0706 (2015-12-15)
            • java: java version "1.8.0_31"
            • linux amd64
            • Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, on linux
            • 3. Re: SQLcl: set sqlformat csv breaks returning into substitution variable
              Gary Graham-Oracle

              No updates yet on that bug.  SQLcl is still Beta with frequent releases as we fix bugs.

              • 4. Re: Re: SQLcl: set sqlformat csv breaks returning into substitution variable
                jmarton

                Things get even worse. The current SQLcl (4.2.0.16.049.0843) on my linux box prompts even to enter value for mydef.

                 

                Steps to reproduce: save the script in the problem description as defvar.sql, and run in SQLcl after connetting to the database.

                 

                The dialog using SQLcl 4.2.0.15.349.0706:

                 

                jmarton@my-laptop:/tmp$ /path/to/oracle/sqlcl-4.2.0.15.349.0706/bin/sql /nolog
                
                SQLcl: Release 4.2.0.15.349.0706 RC on H márc. 07 15:27:27 2016
                
                Copyright (c) 1982, 2016, Oracle.  All rights reserved.
                
                
                SQL> conn username@//my.db.host.example.org:1521/orasid
                Password? (**********?) ********
                Connected
                
                SQL> @defvar
                
                
                VAL   
                ------
                sample
                old:select '&mydef.' from dual
                new:select 'sample' from dual
                
                'SAMPL
                ------
                sample
                
                "OTHERVAL"
                "second sample"
                Enter value for myotherdef I-type-this-for-the-myotherdef-prompt
                old:select '&myotherdef.' from dual
                new:select 'I-type-this-for-the-myotherdef-prompt' from dual
                
                "'I-TYPE-THIS-FOR-THE-MYOTHERDEF-PROMPT'"
                "I-type-this-for-the-myotherdef-prompt"
                SQL> exit
                
                Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
                

                 

                The dialog using current SQLcl 4.2.0.16.049.0843:

                 

                jmarton@my-laptop:/tmp$ /path/to/oracle/sqlcl-4.2.0.16.049.0843/bin/sql /nolog
                
                SQLcl: Release 4.2.0.16.049.0842 RC on H márc. 07 15:27:30 2016
                
                Copyright (c) 1982, 2016, Oracle.  All rights reserved.
                
                SQL> conn username@//my.db.host.example.org:1521/orasid
                Password? (**********?) ********
                Connected.
                SQL> @defvar
                
                VAL   
                ------
                sample
                
                Enter value for mydef I-type-this-now
                old:select '&mydef.' from dual
                new:select 'I-type-this-now' from dual
                
                'I-TYPE-THIS-NO
                ---------------
                I-type-this-now
                
                "OTHERVAL"
                "second sample"
                
                Enter value for myotherdef I-type-this-for-the-second-prompt
                old:select '&myotherdef.' from dual
                new:select 'I-type-this-for-the-second-prompt' from dual
                "'I-TYPE-THIS-FOR-THE-SECOND-PROMPT'"
                "I-type-this-for-the-second-prompt"
                
                SQL> exit
                Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options