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

"Jacob Grydholt Jensen .dk>" <jacob_at_rgrydholt.<REMOVE> wrote in message news:404473a5$0$171$edfadb0f_at_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 Received on Tue Mar 02 2004 - 13:58:19 CET