From: mcstockX@Xenquery   
      
   "Jim Kennedy"  wrote in message   
   news:oqvHb.682774$Fm2.590452@attbi_s04...   
   |   
   | "David"  wrote in message   
   | news:37032184.0312271915.d9d43ef@posting.google.com...   
   | > I am learning plsql. I would like to run a stored procedure to   
   | > calculate my bank account value by predicted 10% annual growth rate.   
   | > Below is my plsql that is having problems. Your help is highly   
   | > appreciated.   
   | >   
   | > Thanks   
   | >   
   | > declare   
   | > money number := 50000.00;   
   | > year number := 1;   
   | > begin for i in 1..17   
   | > loop   
   | > execute immediate 'insert into my_401k values (':year', 'money + 0.10   
   | > * money')';   
   | > year := year + 1;   
   | > end loop;   
   | > end;   
   | > /   
   | >   
   | >   
   | > SP2-0552: Bind variable "YEAR" not declared.   
   | >   
   | > Any where wrong in this script?   
   | >   
   | > Thanks   
   |   
   | Much simpler and more efficient to:(no need for execute immediate)   
   | > declare   
   | > money number := 50000.00;   
   | > year number := 1;   
   | > begin for i in 1..17   
   | > loop   
   | insert into my_401k values (:year, :money + 0.10* :money);   
   | > year := year + 1;   
   | > end loop;   
   | > end;   
   | > /   
   | >   
   |   
   |   
      
   jim's solution is simpler and probably the best way to issue this INSERT,   
   since the statement is not dynamic (only the values)   
      
   however, if you did need to use execute immediate, you could either just   
   concatenate the local PL/SQL variable value (your concatenation operators   
   where missing as well):   
      
   | > execute immediate 'insert into my_401k values ('||year||', '||money +   
   0.10||')'   
      
   or better yet (again, if execute immediate was necessary because the INSERT   
   statement was not known at compile time), use proper bind variable syntax   
   (see the USING keyword in the PL/SQL manual's EXECUTE IMMEDIATE section)   
      
   also, a very important reminder, and an observation   
      
   [_] always (always, always, always) explicitly list the column in your   
   INSERT statement -- never rely on insert into TABLE values (....); as soon   
   as the structure of the table changes, your code breaks   
   [_] do you really need a persistent record of the values stored in the   
   database? if not, don't do database inserts when a simply using PL/SQL   
   variables would suffice   
      
      
   --   
   Mark C. Stock   
   mcstock -> enquery(dot)com   
   www.enquery.com training & consulting   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)