iSQL*Plus bug with substitution variable?! (9.2.0.1.0)

[Jacob Grydholt Jensen]

I am trying to learn iSQL*Plus for the 1Z0-007 exam. I am having a bit of
problems with the substitution variables. My understanding was that they
behave as in SQL*Plus, but my experiments beg to differ.

I log on to the sample schema 'hr' and enter the following statements:

-- statements begin
set verify on
define name = K%

select last_name
from employees
where last_name like '&name'
-- statements end

When I click the 'Execute'-button, I am prompted for the value of the 'name'
substitution variable. Regardless of what I enter, the result is:

-- iSQL*Plus output begin
old 3: where last_name like '&name'
new 3: where last_name like 'K%'
LAST_NAME
King
Kochhar
Khoo
Kaufling
King
Kumar

6 rows selected.
-- iSQL*Plus output end

So my entered value is discarded and the value explicitly defined is always
used, very strange! When executing the same statements in SQL*Plus, a prompt
does not appear.

I tried a few other experiments which showed other differences between
iSQL*Plus and SQL*Plus, so my question is: "Should iSQL*Plus and SQL*Plus
behave the same or are there subtle differences?". If this is a bug, how do
I report it to Oracle?

Best regards,
Jacob Grydholt Jensen

[Mark C. Stock]

"Jacob Grydholt Jensen .dk>" <jacob@rgrydholt.<REMOVE> wrote in message
news:40*********************@dread11.news.tele.dk. ..
| I am trying to learn iSQL*Plus for the 1Z0-007 exam. I am having a bit of
| problems with the substitution variables. My understanding was that they
| behave as in SQL*Plus, but my experiments beg to differ.
|
| I log on to the sample schema 'hr' and enter the following statements:
|
| -- statements begin
| set verify on
| define name = K%
|
| select last_name
| from employees
| where last_name like '&name'
| -- statements end
|
| When I click the 'Execute'-button, I am prompted for the value of the
'name'
| substitution variable. Regardless of what I enter, the result is:
|
| -- iSQL*Plus output begin
| old 3: where last_name like '&name'
| new 3: where last_name like 'K%'
|
|
| LAST_NAME
| King
| Kochhar
| Khoo
| Kaufling
| King
| Kumar
|
| 6 rows selected.
| -- iSQL*Plus output end
|
| So my entered value is discarded and the value explicitly defined is
always
| used, very strange! When executing the same statements in SQL*Plus, a
prompt
| does not appear.
|
| I tried a few other experiments which showed other differences between
| iSQL*Plus and SQL*Plus, so my question is: "Should iSQL*Plus and SQL*Plus
| behave the same or are there subtle differences?". If this is a bug, how
do
| I report it to Oracle?
|
| Best regards,
| Jacob Grydholt Jensen
|
|
|

interesting...

in standard sql*plus (which i'm pretty sure is invoked, or pipe to, by
iSQL*Plus) pre-defined variables are not prompted for

so, i'd say remove the DEFINE and you'll be ok (seems like a bug to me, but
have not researched)

if you're looking for a default value for &name, then use NVL() in your
WHERE clause (you'll most likely need the UPPER function, too), ie:

select last_name
from employees
where last_name like nvl(upper('&name'),'K%')

;-{ mcs