Passing value multiple times to a variable in a pl/sql loop

Question

i have been working with basic pl/sql and need solution for a simple scenario. Insert values to a table where the values are input by the user.

I came up with the following code but it just asks the user for values once and inserts the same value for all the times loop runs. Instead I want it to ask the user to input some value on each loop iteration. Please suggest the right way to do it. Here's my code:

declare 
  n number(2) := &n;
  a abc.id%type;
  b abc.name%type;
  i number(2);
begin
  for i in 1..n
  loop
    a := &a;
    b := &b;
    insert into abc values(a,b);
  end loop;
end;

Here is the Table: abc(id, name)

APC · Accepted Answer · 2015-11-13 07:29:09Z

1

PL/SQL is not an interactive language. It has no concept of prompting users or accepting responses.

This syntax?

a := &a;
b := &b;

It's SQL*Plus, which is a user-facing client. It works when running an anonymous PL/SQL block in SQL*Plus but will fail if you were to convert the code to a stored procedure.

The solution is to choose the approach technology for the task in hand. If what you need is an interactive user-experience write the program in an appropriate UI language; use APEX to keep it PL/SQL. If you need to pass multiple values to an autonomous PL/SQL program write a stored procedure with an input parameter which is a collection type.

answered Nov 13, 2015 at 7:29

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing value multiple times to a variable in a pl/sql loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related