| looping problem in insertion of new records [message #160046] |
Wed, 22 February 2006 23:50  |
sidharthav
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
hi all,
i've written a PL/SQL block which is going to insert multiple recs into a table. Inside the block i've a for loop
and i'am calling a procedure which takes care of inserting a record into the database table. But my problem is that the loop is iterarting only for the first time and its going to terminate.
i want the reason why it is happening like that.
Here is my sample PL?SQL block.
DECLARE
tempvar1 number(4);
tempvar2 number(4);
tempvar3 varchar2(25);
itr number(2);
BEGIN
DBMS_OUTPUT.PUT_LINE('ENTER THE NUMBER OF RECORDS YOU WANT TO ENTER IN SIDHU:');
itr:=&itr;
FOR i in 1..itr LOOP
DBMS_OUTPUT.PUT_LINE('ENTER THE VALUE FOR COL1:');
tempvar1:=&tempvar1;
DBMS_OUTPUT.PUT_LINE('ENTER THE VALUE FOR COL2:');
tempvar2:=&tempvar2;
DBMS_OUTPUT.PUT_LINE('ENTER THE VALUE FOR COL3:');
tempvar3:='&tempvar3';
insertsid(tempvar1,tempvar2,tempvar3);
DBMS_OUTPUT.PUT_LINE('ENTRY OF RECORED NO:'||i||' completed');
END LOOP;
END;
Please help me as ASAP.
Thank Q in Advance.
|
|
|
|
| Re: looping problem in insertion of new records [message #160062 is a reply to message #160046] |
Thu, 23 February 2006 00:45  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
PL/SQL replacement variables (the ones beginning with &) are evaluated only once BEFORE the PL/SQL is executed.
So this block will prompt you for &itr, &tempvar1, &tempvar2, &tempvar3 ONCE ONLY. Then it will loop &itr times, calling insertsid() with the same three values of &tempvar1, &tempvar2, and &tempvar3.
PL/SQL cannot be made to be interactive (and I don't wan't any smart comments from pedants who say it is possible with two processes communicating via named pipes, either).
To make it worse, SQL*Plus provides no native functionality for looping. So the answer is - you can't do it. If you want to load multiple rows into a table, write them to a file and use SQL*Loader.
I have written my own SQL*Plus utilities for FOREACH, WHILE, and IF. Your script would be possible with these utilities. I'd be happy to make it available to anyone interested (send me a PM), but it is very much pre-Beta (ie. if it works, it works well; if it doesn't work, it goes tragically wrong). It's really only for SQL*Plus stalwarts who have an academic interest in how it was done.
_____________
Ross Leishman
|
|
|
|
| Re: looping problem in insertion of new records [message #160063 is a reply to message #160046] |
Thu, 23 February 2006 00:46 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
The loop iterates &itr times alright, but the values of the variables is only asked for once.
The variables are replaced by the entered values at _compile_ time and NOT at runtime.
PL/SQL runs on the server. There is no way to interact with a user once it starts running.
[Edit: too late; must learn to type faster]
[Updated on: Thu, 23 February 2006 04:09] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
