Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

user_2DKLAJun 9 2022 — edited Jun 28 2022

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,

This post has been answered by User_3ABCE on Jun 27 2022
Jump to Answer
Comments
Post Details
Added on Jun 9 2022
2 comments
214 views