NNTP-Posting-Date: Sun, 28 Dec 2003 10:34:52 -0600 Reply-To: "Mark C. Stock" From: "Mark C. Stock" Newsgroups: comp.databases.oracle References: <37032184.0312271915.d9d43ef@posting.google.com> Subject: Re: how to use plsql to calculate compond growth Date: Sun, 28 Dec 2003 11:33:33 -0500 Organization: Enquery, Incorporated X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: <8qOdnZYk6p6xm3KiRVn-jw@comcast.com> Lines: 79 NNTP-Posting-Host: 68.57.99.86 X-Trace: sv3-nPmVGcZNw4mDxCEKHXSHoLD9ZKzhD7Iqwr73BxPJ91x4vsRo70tDkTzXq5pDPeFLN+4UQwdrNplG3o9!xD6/Q51dOJczVFMb8wT8JiBIP7/npmqBv4rkU+ORgerh06EVOD1CqHlKtZk= X-Complaints-To: abuse@comcast.net X-DMCA-Complaints-To: dmca@comcast.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.1 "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