using loop for input vales from user....help needed

i m new with pl/sql...just started learning...

my problem is that as in c or c++ we can accept vales from user as many times by usin loops, i want to do same using pl/sql block but something is going wrong...the initial value that i enterd gets printed till the loop ends...
bellow is the block of code...pls correct me if i ma doing wrong..
thank u in advance

declare
n number;
i number;
begin
i:=1;
while(i<5)
loop
n:=&n;
dbms_output.put_line(n);
n:=0;
i:=i+1;
end loop;
end;
/


o/p is
1
1
1
1

Comments for using loop for input vales from user....help needed

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 23, 2015
Rating
starstarstarstarstar
PL/SQL is not designed to be interactive
by: John

The problem is you're using a SQL*Plus command within PL/SQL. The &n causes SQL*Plus to prompt for a value - but only once! The next time through the loop, SQL*Plus already has a value for n so it dopesn't pronpt for teh value to be entered.

PL/SQL is not designed to be interactive. Rather it is designed to lend some structure to SQL. Facilities in PL/SQL for direct interaction with an end user are very limited. There is no DBMS_INPUT, or DDBMS_READ package to read from the terminal. You could use UTL_FILE.GET_LINE to read the required values from a file but the file has to be accessible from the system running the Oracle database and cleint-side locatons generally do not fall into this category.

Another way to make PL/SQL interactive would be to use a tool that is designed to interact with the client such as Oracle Forms or Oracle Application Express but then PL/SQL would be just a small part of the application.

For more details on PL/SQL see http://www.asktheoracle.net/plsql-tutorial.html.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.