CODEWITHSUNDEEP
sqloracle-databasevariablesplsqlvariable-assignment
Manpreet Singh
Manpreet Singh

Reputation: 1

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

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)

Upvotes: 0

Views: 1346

Answers (1)

APC
APC

Reputation: 146239

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.

Upvotes: 1

Related Questions