Forum Stats

  • 3,837,263 Users
  • 2,262,245 Discussions
  • 7,900,240 Comments

Discussions

SQLcl 22.1 does not handle && (double ampersand) substitutions correctly [FIXED in 22.2]

User_2DKLA
User_2DKLA Member Posts: 47 Blue Ribbon
edited Jun 28, 2022 10:01AM in SQLcl

Hello,

Apparently since SQLcl 22.1.0:

SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.1.0 build: 22.1.1.131.0820

SQL> define _DATE
DEFINE _DATE           = "2022-06-09 13:09:03" (CHAR)

SQL> prompt Date is now: &&_DATE
Date is now: &2022-06-09 13:09:15

The & symbol before 2022 in the readout is not expected at all, as the substituted token here should be &&_DATE, not &_DATE.

SQLcl 21.4.1 worked as expected:

SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.1.0 build: 21.4.1.17.1458

SQL> define _DATE
DEFINE _DATE           = "2022-06-09 13:07:42" (CHAR)

SQL> prompt Date is now &&_DATE
Date is now 2022-06-09 13:07:58

Perhaps worse: SQLcl 22.1 does not automatically DEFINE substitution variables preceded by two ampersands (as it should):

SQL> define MYVAR
SP2-0135: symbol myvar is UNDEFINED

SQL> prompt MYVAR = &&MYVAR
Enter value for MYVAR: test
MYVAR = &test

SQL> prompt MYVAR = &&MYVAR
Enter value for MYVAR: _  

SQLcl 22.1 re-prompts for the value of the MYVAR substitution variable, which means it did not DEFINE it automatically the first time &&MYVAR was replaced. Again, this is not expected behaviour—but is consistent with the fact that it did not recognize &&MYVAR as a single token in the first place, as it apparently mistook it for a single & character followed by &MYVAR.

Obviously, this looks like a bug / regression. 😕

Regards,

Best Answer

  • User_3ABCE
    User_3ABCE Member Posts: 41 Blue Ribbon
    Answer ✓

    22.2 Release Notes

    ...

    Bugs Fixed

    ...

    34056508   DOUBLE AMPERSANDS FOR VARIABLES NO LONGER WORKING

    ...

    SQL> show version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.2.0.0 build: 22.2.0.172.1718
    SQL> define x=123
    SQL> prompt &&x
    123
    

Answers