How to disable variable substitution in SQL window?

[Ivan C.]

Executing the following query in a SQL Window (simplified for testing):

SELECT * FROM dual WHERE 'A&B' = 'A&B';

will prompt me with a "Variables" window, asking me to specify a value for B.

Is there a way to suppress variable substitution in a SQL Window, so the above SQL executes without prompting for a value?

Thank you in advance.
Ivan C.

 

[cassiusdrow]

I don't believe there is. You can workaround this by using one of these techniques:

SELECT * FROM dual WHERE 'A&' || 'B' = 'A&' || 'B';

SELECT * FROM dual WHERE 'A&&B' = 'A&&B';

Seperate the ampersand from the next character or use a double ampersand. The first technique also works in sql*plus, but the second does not.

 

[CTzen]

Easy - SET DEFINE OFF
To turn it back - SET DEFINE &

Not easy, I'm taking it back. Adding this command to AfterConnect.sql doesn't help either.

 

[Ivan C.]

That's good to know. Thanks.
It actually works if the character after & is anything else but a number or an A to Z letter.

But aside from that, it would be nice to have an ON/OFF feature, like SET DEFINE ON and SET DEFINE OFF in SQL*Plus.

Marco, any comments?

Thanks.
Ivan C.

 

[Marco Kalter]

As stated by cassiusdrow, the only option currently is to use the && pair. I have added an on/off option to the list of enhancement requests.

 

[a_kassabian]

This is really annoying. I am using the HTP package to generate HTML and the ampersand (&) is used all over the place. Can I use SET DEFINE OFF to turn this off for my sessions automatically?

Ara

 

[Gary Wong]

Ara,

I notice that the 'Command Window' will accept the 'SET DEFINE OFF' command. Can you run your HTP package in the Command window instead of the SQL Window?

HTH
gary

 

[a_kassabian]

I was able to compile my package by changing the window to a command window and adding the SET DEFINE OFF at the top, so that's OK. I guess I have to user ALTER SESSION ... for the SQL windows but I guess that is livable as well. Just go caught off guard after the upgrade to version 7.

Ara

 

[Ivan C.]

Marco,

Thanks for adding it to the list of enhancement requests.

Just as a note to anyone interested, the "&& workaround" doesn't work when checked against a real value in the table. For example:

SQL> create table company_temp (company_name varchar (30));

Table created

SQL> set define off
SQL> insert into company_temp values ('A&B Construction');

1 row inserted

SQL> commit;

Commit complete

SQL>

Now, open a SQL Window, and type the following statement:

select * from company_temp where company_name like '%A&&B%';

No records returned.

Now try this (from a new Command Window):

SQL> insert into company_temp values ('A&&B Services');

1 row inserted

SQL> select company_name from company_temp;

COMPANY_NAME
------------------------------
A&B Construction
AB Services

SQL> rollback;

Rollback complete

SQL>

Instead, if you try the same insert from a SQL Window (commit your changes, then go to the Command Window), you get:

SQL> select company_name from company_temp;

COMPANY_NAME
------------------------------
A&B Construction
A&&B Services

SQL>

Regards,
Ivan C.

 

[Marco Kalter]

This seems like a bug. We'll check it out.

 

[cp]

Just installed 7.0.1.1066 and this is still an issue.

Escaping the substitution as above (...like '%A&&B%') used to work in 6.x but simply returns no records in all 7.0.x versions so far.

 

[Marco Kalter]

Yes, this one unfortunately slipped us by. We'll have it fixed for the next patch release though. If you need a pre-release for this fix, let me know.

 

[MikeBee_ESI_PLSQLDevelop]

Grr. I was wrong about '\' escape working in SQL Window
(does in Command window though)

-Regards

 

[cp]

Would just like to report this is fixed in v7.0.2.1076. Thanks