Forum Stats

  • 3,750,233 Users
  • 2,250,137 Discussions
  • 7,866,845 Comments

Discussions

Substitution Variables

Piotr Wrzosek
Piotr Wrzosek Member Posts: 71 Blue Ribbon
edited Nov 14, 2017 1:57PM in SQLcl

Just noticed two differences how sql*plus and sqlcl handle substitution variables.

First, definition:

sql*plus:

SQL>define var1 = "1"

SQL>define

DEFINE VAR1            = "1" (CHAR)

sqlcl:

SQL>define var1 = "1"

SQL>define

DEFINE VAR =  1 (NUMBER)

Second, substitution in SQL comments:

sql*plus:

SQL> select /* &var1 */ 'sqlplus' from dual;

old   1: select /* &var1 */ 'sqlplus' from dual

new   1: select /* 1 */ 'sqlplus' from dual

'SQLPLU

-------

sqlplus

sqlcl:

SQL> select /* &var1 */ 'sqlcl' from dual;

'SQLC

-----

sqlcl

SQL> select sql_text from v$sql where sql_text like '%sqlcl%';

SQL_TEXT

--------------------------------------------------------------

select /* &var1 */ 'sqlcl' from dual

This is more troubling, as it's commonly used for forcing parse of SQL.

Is that expected behavior?

Answers

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Nov 13, 2017 10:57AM

    Even with set classic on it seems that SQLcl treats a comment as a comment without any variable substitution.  In general, that strikes me as a pretty good idea.

    The documentation for SQL*Plus explicitly supports variable substitution in comments.  See Notes on Placing Comments in...

    https://docs.oracle.com/database/121/SQPUG/ch_five.htm#SQPUG427

    which even warns to take care when using ampersands within comments.  So it looks like SQLcl does not support your case...

    This is more troubling, as it's commonly used for forcing parse of SQL.

    Starting with Oracle 11g however (and a patch set for 10.2.0.4), the DBMS_SHARED_POOL.PURGE procedure is available to flush a single SQL statement out of the object library cache, thereby forcing a hard parse upon the next execution.

  • Piotr Wrzosek
    Piotr Wrzosek Member Posts: 71 Blue Ribbon
    edited Nov 14, 2017 2:57AM

    Thank you for confirmation.

    Do you know if it will stay that way or will it be unified for sake of compatibility?

    I know about PURGE, actually I was considering using it yesterday, but for my case it would be too much hassle comparing to much easier comment solution.

    Both this and not implemented autotrace traceonly keep me from using sqlcl for running test cases.

    It's this vs output formatting in sql*plus...

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Nov 14, 2017 1:57PM
    Do you know if it will stay that way or will it be unified for sake of compatibility?

    All I can say is, so far, no bug has been logged on this issue.  Perhaps it is an oversight, or you are the first to hit this specialized use case, or maybe most feel, as I do, that treating a comment as a comment and prohibiting substitution is preferable.  In any event, SQL*Plus is not going anywhere and will be available if you really want to use this trick to do hard parsing without first purging the statement from the cache.