How to use loop to insert records of data into table?

Question

I would like to insert a couple of records using PL/SQL loops, but when I execute below block in SQL Developer I am getting the following error:

declare
    loop_size number;

begin
    loop_size := 10;

    while loop_size > 0
    loop
        INSERT INTO CM.CUSTOMER (ID, FIRST_NAME, LAST_NAME, STATUS )
        VALUES (CM.SQ_CUSTOMER.nextval, 'Jhon', 'Jhon',10);

        loop_size := loop_size -1;      
    end loop;
end;

Error

PLS-00225: subprogram or cursor 'CM' reference is out of scope

So will you be running this as another user? Generally it is best practice not to hardcode schema names unless you really have to. I would prefer to leave out the schema name and run it from the CM account. (By the way, you have a status variable but you don't use it. Is it a sting or a number?) — William Robertson
– William Robertson, Commented Dec 23, 2018 at 17:33

William Robertson · Accepted Answer · 2018-12-23 18:02:10Z

2

I couldn't reproduce your error, but one problem is that CM.CUSTOMER can't be both a table and a sequence.

Generally it is best practice not to hardcode schema names unless you really have to. I would prefer to leave out the schema name here and just run it from the CM account.

I created a sequence named customer_seq and the following runs without error:

create table customer
( id          number primary key 
, first_name  varchar2(20)
, last_name   varchar2(20)
, status      number );

create sequence customer_seq;

declare
    status varchar(10) := '10';
    loop_size number := 10;
begin
    while loop_size > 0
    loop
        insert into customer (id, first_name, last_name, status)
        values (customer_seq.nextval, 'Jhon', 'Jhon', status);

        loop_size := loop_size -1;      
    end loop;
end;
/

If using Oracle 12.1 or later I would prefer to use an identity column and stop micro-managing sequences. You also don't need to manage the loop index yourself as PL/SQL has a convenient for loop construct:

create table customer
( id          number generated always as identity primary key 
, first_name  varchar2(20)
, last_name   varchar2(20)
, status      number );

declare
    status varchar(10) := '10';
    loop_size number := 10;
begin
    for i in 1..loop_size loop
        insert into customer (first_name, last_name, status )
        values ('Jhon', 'Jhon', status);
    end loop;
end;
/

edited Dec 23, 2018 at 18:02

answered Dec 23, 2018 at 17:56

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

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

1 Comment

APC Over a year ago

Good spot that the OP had used CM.CUSTOMER for both table and sequence name.

Tim Biegeleisen · Accepted Answer · 2018-12-23 09:20:46Z

1

You may achieve this without dynamic SQL:

INSERT INTO CM.CUSTOMER (FIRST_NAME, LAST_NAME, STATUS)
SELECT 'Jhon', 'Jhon', '10'
FROM dual
CONNECT BY rownum <= 10;

This answer assumes that the ID column be auto increment, which it probably should be.

edited Dec 23, 2018 at 9:20

answered Dec 23, 2018 at 8:27

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

APC Over a year ago

Umm, in the OP's version value of STATUS is 10.

Ori Marko · Accepted Answer · 2018-12-24 11:09:09Z

0

Remove last unnecessary commas and select sequence from dual:

  INSERT INTO CM.CUSTOMER (ID, FIRST_NAME, LAST_NAME, STATUS)
  select ln.SQ_CUSTOMER, 'Jhon', 'Jhon', 10 from dual;

update to sequence to ln.SQ_CUSTOMER

edited Dec 24, 2018 at 11:09

answered Dec 23, 2018 at 8:25

Ori Marko

59.3k26 gold badges157 silver badges259 bronze badges

3 Comments

Littlefoot Over a year ago

That won't work, Either remove VALUES, or remove SELECT & FROM DUAL.

William Robertson Over a year ago

Also CM.CUSTOMER can't be both a table and a sequence.

Davoud Over a year ago

@user7294900, it seems, doesn't recognize schema ln, I get the following error: PLS-00225: subprogram or cursor 'LN' reference is out of scope

Davoud · Accepted Answer · 2022-02-10 07:58:11Z

0

Many thanks for your responses, finally I solved my problem as bellow:

declare
loop_size number;
rec_id NUMBER;

begin
  loop_size := 10;

  while loop_size > 0
  loop

    execute immediate 'select cm.SQ_CUSTOMER.nextval from dual' into rec_id;

    INSERT INTO CM.CUSTOMER (ID, FIRST_NAME, LAST_NAME, STATUS )
    VALUES (rec_id, 'Jhon', 'Jhon',10);

    loop_size := loop_size -1;      
  end loop;
end;

edited Feb 10, 2022 at 7:58

answered Dec 24, 2018 at 10:01

Davoud

3,0332 gold badges39 silver badges67 bronze badges

2 Comments

Ori Marko Over a year ago

See my updated answer without using execute immediate

William Robertson Over a year ago

Why are you using execute immediate for the (pre-12c style) sequence? (And why is it in a different schema from the table?) You can just assign it like your other variables: rec_id := sq_customer.nextval. Or better, just use it directly in the insert statement with no need for any intermediate variable. Or even better, use an identity column and forget about sequences (see my answer).

Collectives™ on Stack Overflow

How to use loop to insert records of data into table?

4 Answers 4

1 Comment

1 Comment

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related