Substitution variables within cursors

[miel]

Hi
I have a cursor that is an ordinary select statement, I open the cursor, and perform an update within each loop of the cursor, however in the update I use a substitution variable. When I run this script, it works, however it prompts the user for the substitution variable once and uses that value in each loop of the cursor - I could do this in a different way perhaps through unix shell&sql or simpler sql- but I was just wondering why this didn't work ?

DECLARE CURSOR ChargeQuery IS
Select sb.servicebundleid,o.operatorid,sb.bundlename,sb.bundlecharge
from service_bundles sb,operator o;

vbundleid number;
vbundlename varchar2(40);
vbundlecharge number;
vopid number;
BEGIN
OPEN ChargeQuery;
LOOP
FETCH ChargeQuery INTO vbundleid,vopid,vbundlename,vbundlecharge;
EXIT WHEN ChargeQuery%NOTFOUND;
BEGIN
Update service_bundles
set bundlecharge=&icharge
where servicebundleid=vbundleid
and operatorid =vopid;
END;

END LOOP;
CLOSE ChargeQuery;
END;
/


thanks
Martha [sig][/sig]

 

[connectxyzzy]

I think that you are using sql*plus substitution variable, and when you begin execution of that PL/SQL statement, the subs. occurs once because the entire PL/SQL statement is just executing once. If you re-run 'the entire PL/SQL' again, it would subs. once again. Just the way sql*plus 'sees' the statement. [sig][/sig]

 

[MikeLacey]

Martha,

As I remember if you say [tt]&&icharge[/t] (notice that the & is repeated) it will only ask the once.. [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]