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 11 7799
"David" <ni*******@yaho o.com> wrote in message
news:37******** *************** **@posting.goog le.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 Kennedy" <ke************ *************** *@attbi.net> wrote in message
news:oqvHb.6827 74$Fm2.590452@a ttbi_s04...
|
| "David" <ni*******@yaho o.com> wrote in message
| news:37******** *************** **@posting.goog le.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
Hi Jim,
Thank you for your help. I still got the same error message based on your code
Any where wrong?
Thanks
David
1 declare
2 money number :=50000.00;
3 year number :=1;
4 begin for i in 1..17
5 loop insert into my_401k values(:year, :money + 0.1*:money);
6 year := year +1;
7 end loop;
8* end;
nli@HOPE1> /
SP2-0552: Bind variable "MONEY" not declared. ni*******@yahoo .com (David) wrote in message news:<37******* *************** ***@posting.goo gle.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
David wrote: Hi Jim,
Thank you for your help. I still got the same error message based on your code
Any where wrong?
Thanks
David
1 declare 2 money number :=50000.00; 3 year number :=1; 4 begin for i in 1..17 5 loop insert into my_401k values(:year, :money + 0.1*:money); 6 year := year +1; 7 end loop; 8* end; nli@HOPE1> / SP2-0552: Bind variable "MONEY" not declared.
ni*******@yahoo .com (David) wrote in message news:<37******* *************** ***@posting.goo gle.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
Since this is a PL/SQL variable, does it need to be bound? What happens
when you drop the semi-colons?
What I understand from bind variables is they are used to bind between
the host environment (eg C, VB, SQL*Plus) and the SQL & PL/SQL
environment. Flames for correction appreciated.
/Hans
PL SQL autobinds so no need to specify those as bind variables
This should work
1 declare
2 money number :=50000.00;
3 year number :=1;
4 begin for i in 1..17
5 loop insert into my_401k values(year, money + 0.1*money);
6 year := year +1;
7 end loop;
8* end;
Hope it Helps
nullpointer
*************** *************** *************** ** ni*******@yahoo .com (David) wrote in message news:<37******* *************** ****@posting.go ogle.com>... Hi Jim,
Thank you for your help. I still got the same error message based on your code
Any where wrong?
Thanks
David
1 declare 2 money number :=50000.00; 3 year number :=1; 4 begin for i in 1..17 5 loop insert into my_401k values(:year, :money + 0.1*:money); 6 year := year +1; 7 end loop; 8* end; nli@HOPE1> / SP2-0552: Bind variable "MONEY" not declared.
ni*******@yahoo .com (David) wrote in message news:<37******* *************** ***@posting.goo gle.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 nu**********@re diffmail.com (nullpointer) wrote in
news:c0******** *************** ***@posting.goo gle.com: PL SQL autobinds so no need to specify those as bind variables This should work
1 declare 2 money number :=50000.00; 3 year number :=1; 4 begin for i in 1..17 5 loop insert into my_401k values(year, money + 0.1*money); 6 year := year +1; 7 end loop; 8* end;
Hope it Helps nullpointer
That will get around the error message, but it won't give you the results
you're looking for. Note: you never changed the value of the variable
money, so every year money is going to have 55000.00 in the moey column.
--
Ken Denny http://www.kendenny.com/
Hans Forbrich wrote: David wrote:
Hi Jim,
Thank you for your help. I still got the same error message based on your code
Any where wrong?
Thanks
David
1 declare 2 money number :=50000.00; 3 year number :=1; 4 begin for i in 1..17 5 loop insert into my_401k values(:year, :money + 0.1*:money); 6 year := year +1; 7 end loop; 8* end; nli@HOPE1> / SP2-0552: Bind variable "MONEY" not declared.
ni*******@yah oo.com (David) wrote in message news:<37******* *************** ***@posting.goo gle.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 appreciate d.
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
Since this is a PL/SQL variable, does it need to be bound? What happens when you drop the semi-colons?
What I understand from bind variables is they are used to bind between the host environment (eg C, VB, SQL*Plus) and the SQL & PL/SQL environment. Flames for correction appreciated.
/Hans
No - it does not need binding; just loose the ':' (and quotes):
insert into my_401k values(year, 1.1*money) ;
The exec imm *can* use bind variables:
execute immediately ('insert into my_401k values(:y, :m)'
using year, money * 1.1;
Have never used the using money * 1.1 sytax though, so it's
without warrenty.
Oh - heck:
SQL> create table my_401k (year number, money number(12,2));
1 declare
2 money number := 50000.00;
3 year number := 1;
4 begin
5 for i in 1..17
6 loop
7 execute immediate 'insert into my_401k values (:y,:m)'
8 using year, money * 1.1;
9 year := year + 1;
10 end loop;
11* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from my_401k;
YEAR MONEY
---------- ----------
1 55000
2 55000
3 55000
4 55000
5 55000
6 55000
7 55000
8 55000
9 55000
10 55000
11 55000
YEAR MONEY
---------- ----------
12 55000
13 55000
14 55000
15 55000
16 55000
17 55000
--
A prosperous 2004,
Regards,
Frank van Bortel
Ken Denny wrote: nu**********@re diffmail.com (nullpointer) wrote in news:c0******** *************** ***@posting.goo gle.com:
PL SQL autobinds so no need to specify those as bind variables This should work
1 declare 2 money number :=50000.00; 3 year number :=1; 4 begin for i in 1..17 5 loop insert into my_401k values(year, money + 0.1*money); 6 year := year +1; 7 end loop; 8* end;
Hope it Helps nullpointer
That will get around the error message, but it won't give you the results you're looking for. Note: you never changed the value of the variable money, so every year money is going to have 55000.00 in the moey column.
1 declare
2 money number := 50000.00;
3 year number := 1;
4 begin
5 for i in 1..17
6 loop
7 money := money *1.1;
8 execute immediate 'insert into my_401k values (:y,:m)'
9 using year, money;
10 year := year + 1;
11 end loop;
12* end;
SQL> col money for 999,999,990.00
SQL> select * from my_401k;
YEAR MONEY
---------- ---------------
1 55,000.00
2 60,500.00
3 66,550.00
4 73,205.00
5 80,525.50
6 88,578.05
7 97,435.86
8 107,179.44
9 117,897.38
10 129,687.12
11 142,655.84
YEAR MONEY
---------- ---------------
12 156,921.42
13 172,613.56
14 189,874.92
15 208,862.41
16 229,748.65
17 252,723.51
17 rows selected.
--
A prosperous 2004,
Regards,
Frank van Bortel
Frank van Bortel <fv********@net scape.net> wrote in message news:<bs******* ***@news2.tilbu 1.nb.home.nl>.. . Ken Denny wrote:
nu**********@re diffmail.com (nullpointer) wrote in news:c0******** *************** ***@posting.goo gle.com:
PL SQL autobinds so no need to specify those as bind variables This should work
1 declare 2 money number :=50000.00; 3 year number :=1; 4 begin for i in 1..17 5 loop insert into my_401k values(year, money + 0.1*money); 6 year := year +1; 7 end loop; 8* end;
Hope it Helps nullpointer
That will get around the error message, but it won't give you the results you're looking for. Note: you never changed the value of the variable money, so every year money is going to have 55000.00 in the moey column. 1 declare 2 money number := 50000.00; 3 year number := 1; 4 begin 5 for i in 1..17 6 loop 7 money := money *1.1; 8 execute immediate 'insert into my_401k values (:y,:m)' 9 using year, money; 10 year := year + 1; 11 end loop; 12* end;
SQL> col money for 999,999,990.00 SQL> select * from my_401k;
YEAR MONEY ---------- --------------- 1 55,000.00 2 60,500.00 3 66,550.00 4 73,205.00 5 80,525.50 6 88,578.05 7 97,435.86 8 107,179.44 9 117,897.38 10 129,687.12 11 142,655.84
YEAR MONEY ---------- --------------- 12 156,921.42 13 172,613.56 14 189,874.92 15 208,862.41 16 229,748.65 17 252,723.51
17 rows selected.
Frank,
Thank you very much. That is exactly I was looking for.
Have a Happy and Prosporous New Year!
David This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Corrine |
last post by:
I am trying to pass a temporary BLOB that I've created in Java, to a
PLSQL function that modifies it, and then to retrieve this modified
value back to Java, all using JDBC.
I can't figure out how to get the modified BLOB object back to Java
after the PLSQL block has finished executing.
I do:
BLOB bl = BLOB.createTemporary(db,true,BLOB.DURATION_SESSION);
|
by: eric.nave |
last post by:
In a situation where you have two tables in a hierarchy like this:
create table authors (authorid int identity (1,1))
create table books (
authorid int,
bookid int identity (1,1)
)
Is there any disadvantage to having the primary key and the clustered
|
by: marvado |
last post by:
Hi,
can I run phpinfo(); or any php code from an oracle plsql package using
htp.p from the oracle web toolkit owa?
what I need is to run any php code using htp.p()
I might be missing something, in my oracle/apache/php configuration I
can only run flat .php files from /htdocs but can't run any php from a
plsql procedure to display on a browser with htp.p
|
by: Tom |
last post by:
Hello, please help me, I need two write a C++ program that can
calculate the number of days between two dates.
|
by: kux |
last post by:
Hello everyone,
I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by
product. to make future predictions I need the slope (representing
growth) of monthly sales.
the data is setup in a crosstab with the months in different collums,
say column 1 to 12 for last year. I want to calculate the slope for
every row (on the same query or another if needed)
| |
by: kux |
last post by:
Hello everyone,
I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by
product. to make future predictions I need the slope (representing
growth) of monthly sales.
the data is setup in a crosstab with the months in different collums,
say column 1 to 12 for last year. I want to calculate the slope for
every row (on the same query or another if needed)
|
by: anuptosh |
last post by:
Hi,
I have been trying to run the below example to get a Oracle Array as an output from a Java code. This is an example I have found on the web. But, the expected result is that the code should return me Array element type code 1, but it is returning me type code 12 and the array in a junk or unreadable format . Our environment is JDK 1.4, ojdbc14.jar and Oracle 8i
Not able to find out what they problem is and am not able to change the...
|
by: sybrandb |
last post by:
"Jorge Pinto" <jorgep@sympatico.cawrote in message news:<L2HQa.3116$104.264170@news20.bellglobal.com>...
utl_smtp is only a wrapper for a java procedure. Need I say more.
And oh yes, you may need to check your DBA actually configured the
java_pool_size, or he is going to be in for some nasty surprises.
Sybrand Bakker
Senior Oracle DBA
|
by: David |
last post by:
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;
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |