From: mcstockX@Xenquery   
      
   "Jacob Grydholt Jensen .dk>"  wrote in message   
   news:404473a5$0$171$edfadb0f@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)