9 Replies Latest reply on Mar 13, 2016 11:28 AM by Erik van Roon

    SqlCL and new_value

    Erik van Roon

      Jeff recently said on his blog: "and we’re super close to 100% SQL*Plus support now."

       

      Great! But since it's not 100% yet, may I suggest a lacking feature to be one of the first to pick up next?

       

      In SQL*Plus the 'column' command has an option 'new_value' which enables you to put the value of a column, selected in a query, to be put into a variable.

      (see below for example)

      In sqlcl 'new_value' appears to be accepted as an option for 'column', since no syntax-error is reported.

      It doesn't work however.

       

      This is a shame.

      I have quite a number of scripts utilizing this feature, and they do not work in sqlcl.

      This is the main reason for me to keep going back to sql*plus.

       

      Example sql*plus (12.1.0.2.0)

      EVANROON@DEV>column my_schema new_value current_schema

      EVANROON@DEV>select user my_schema from dual;

       

      1 row selected.

       

      EVANROON@DEV>select '&current_schema' var_value from dual;

       

      VAR_VALU

      --------

      EVANROON

       

      1 row selected.

       

       

      Example sqlcl (feb 18, 2016)

      SQL> column my_schema new_value current_schema

      SQL> select user my_schema from dual;

       

      1 rows selected.

       

      SQL> select '&current_schema' var_value from dual;

      Enter value for current_schema

        • 1. Re: SqlCL and new_value
          thatJeffSmith-Oracle

          It's not a shame, just a bug. I'm pretty sure this was working before, so it's not a deliberate omission.

          • 2. Re: SqlCL and new_value
            Gary Graham-Oracle

            As Jeff suggests, if you revert to an older version of SQLcl, say sqlcl-4.2.0.15.349.0706-no-jre from mid-December, the test case works.

            1 person found this helpful
            • 3. Re: SqlCL and new_value
              RJLyders

              FYI: I find that it works with "&&" as opposed to "&" within the context of a single SQL file. Called SQL files appear to lose anything defined with new_value as reported here: SQLcl: new_value and @: variable defined by new_value in caller script is unknown in called script

              1 person found this helpful
              • 4. Re: SqlCL and new_value
                Erik van Roon

                Thanks all for the feedback.

                 

                Confirmed. Just tried this on the dec 15 version, and it works there.

                 

                @RJLyders : Thanks for the workaround. But I have several 'utility' scripts that get information and put it in variables, so these scripts can be called by other scripts that need this information. So these won't work, even with the workaround.

                 

                I guess I'll just have to wait for the next version (previous one had other issues for me).

                 

                Still the tool is becoming more and more awesome.

                Can't wait for the day I can make a 100% transition (I'd even be prepared to rewrite some of my scripts if needed).

                • 5. Re: SqlCL and new_value
                  Richard Soule

                  Where did you get the Dec 15th version? I was about to log this new_value issue, but found this thread... I'd like to have it working until it is patched.

                  • 6. Re: SqlCL and new_value
                    Gary Graham-Oracle

                    Well, clicking on the Previous Version link on the line of the 4.1.3 Downloads page that says Troubleshooting - Previous Version should work, but unfortunately that will take you back to the 4.1.1 release page which lists the Oct 13, 2015 version of SQLcl.


                    So unless someone else knows where to find that standalone version, your best bet is to download the full SQL Developer 4.1.3 which includes that version of SQLcl and use that.  For convenience, you may wish to copy the relevant jar files from there, replacing the newer ones in your standalone SQLcl version's lib directory. 


                     

                    • 7. Re: SqlCL and new_value
                      Erik van Roon

                      Richard,

                       

                      I got it from the standard location somewhere between dec 15 and feb 18

                      But I guess the real question you're asking is: ""where can I get it now?".

                       

                      I always keep the old versions of my software, so....

                      I am perfectly happy to provide you a link to that version on my personal storage.

                      However, I am not 100% sure that by doing so I wouldn't be making Oracle unhappy.

                      I know it's free stuff, but still, to download the software you need an otn account and need to accept the terms and conditions.

                      Giving you a download link to my downloaded software bypasses this all together.

                       

                      So, if someone (Jeff???) could convince me I wouldn't be doing illegal stuff, I'll post a link.

                       

                      Erik


                      • 8. Re: SqlCL and new_value
                        thatJeffSmith-Oracle

                        Since you asked, I have to reply, no, you shouldn't be doing that. And if you do do it, don't talk about it here.

                         

                        The real solution will be posting an update with this, and many other bugs fixed. Stay tuned.

                        • 9. Re: SqlCL and new_value
                          Erik van Roon

                          Thanks Jeff.

                          That's what I feared.

                          And that's the problem with asking: now I can't say "Sorry, didn't know" anymore

                           

                          Anyway, Richard, I think we just need to wait a little more. Probably not too long.