PLSQL procedure to insert data from one table to another, error shows SQL command not ended properly

Question

I am trying to insert data from one table to another through a plsql stored procedure which will create a table and then insert. But I get an error that SQL command is not ended properly. Please suggest.

My stored procedure is:

 create or replace show errors procedure persons_insert 
    as

    a1 int;
    id1 int;
    ac int;
    fn varchar2(20);
    mn varchar2(20);
    ln varchar2(20);
    gen varchar2(3);
    ey varchar2(10);
    col varchar2(10);
    h_cms int;
    w_ps int;
    dob date;
    ms varchar2(20);
    jt varchar2(30);
    dep varchar2(30);
    hd date;
    aad number(30);
    sta varchar2(20);
    cx int;

    begin
    a1:=1;
    id1:=1;
    ac:=1;
    EXECUTE IMMEDIATE  'create table Persons_data as select * from persons where 1=2';
    execute immediate 'alter table persons_data add primary key(person_id)';

    while a1 < 100 loop
    select count(person_id) into cx from persons;

    if ac>cx then
    ac:=1;
    end if;

    select firstname from(select firstname,row_number() over(order by person_id)as rn from persons)tmp into fn where rn=ac;
    select middlename from(select middlename,row_number() over(order by person_id)as rn from persons)tmp into mn where rn=ac;
    select lastname from(select lastname,row_number() over(order by person_id)as rn from persons)tmp into ln where rn=ac;
    select gender from(select gender,row_number() over(order by person_id)as rn from persons)tmp into gen where rn=ac;
    select eyes from(select eyes,row_number() over(order by person_id )as rn from persons)tmp into ey where rn=ac;
    select color from(select color,row_number() over(order by person_id)as rn from persons)tmp into col where rn=ac;
    select height_cms from(select height_cms,row_number() over(order by person_id)as rn from persons)tmp into h_cms where rn=ac;
    select weight_pounds from(select weight_pounds,row_number() over(order by person_id)as rn from persons)tmp into w_ps where rn=ac;
    select date_of_birth from(select date_of_birth,row_number() over(order by person_id)as rn from persons)tmp into dob where rn=ac;
    select marital_status from(select marital_status,row_number() over(order by person_id)as rn from persons)tmp into ms where rn=ac;
    select job_title from(select job_title,row_number() over(order by person_id )as rn from persons)tmp into jt where rn=ac;
    select department from(select department,row_number() over(order by person_id)as rn from persons)tmp into dep where rn=ac;
    select hiredate from(select hiredate,row_number() over(order by person_id )as rn from persons)tmp into hd where rn=ac;
    select aadhar_no from(select aadhar_no,row_number() over(order by person_id)as rn from persons)tmp into aad where rn=ac;
    select status from(select status,row_number() over(order by person_id)as rn from persons)tmp into sta where rn=ac;

    insert into persons_data(Person_id,Firstname,Middlename,Lastname,Gender,Eyes,Color,Height_cms,weight_pounds,Date_of_birth,Marital_status,Job_Title,
    Department,hiredate,Aadhar_no,Status) values (id1,fn,mn,ln,gen,ey,col,h_cms,w_ps,dob,ms,jt,dep,hd,aad,sta);
    commit;

    id1:=id1+1;
    ac:=ac+1;
    a1:=a1+1;
    end loop;
    EXCEPTION  -- exception handlers begin
      WHEN OTHERS THEN  -- handles all other errors
              DBMS_OUTPUT.PUT_LINE (SQLCODE|| ' ' || SQLERRM);
    END; 

    end;

Error report:

ORA-06550: line 15, column 100: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 15, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 16, column 102: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 16, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 17, column 98: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 17, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 18, column 94: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 18, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 19, column 91: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 19, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 20, column 92: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 20, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 21, column 102: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 21, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 22, column 108: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 22, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 23, column 108: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 23, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 24, column 110: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 24, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

After changes:



create or replace show errors procedure persons_insert 
    as

    a1 int;
    id1 int;
    ac int;
    fn varchar2(20);
    mn varchar2(20);
    ln varchar2(20);
    gen varchar2(3);
    ey varchar2(10);
    col varchar2(10);
    h_cms int;
    w_ps int;
    dob date;
    ms varchar2(20);
    jt varchar2(30);
    dep varchar2(30);
    hd date;
    aad number(30);
    sta varchar2(20);
    cx int;

    begin
    a1:=1;
    id1:=1;
    ac:=1;
    EXECUTE IMMEDIATE  'create table Persons_data as select * from persons where 1=2';
    execute immediate 'alter table persons_data add primary key(person_id)';

    while a1 < 100 loop
    select count(person_id) into cx from persons;

    if ac>cx then
    ac:=1;
    end if;

    select firstname into fn from(select firstname,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select middlename  into mn from(select middlename,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select lastname into ln from(select lastname,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select gender into gen from(select gender,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select eyes  into ey from(select eyes,row_number() over(order by person_id )as rn from persons)tmp where rn=ac;
    select color into col from(select color,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select height_cms into h_cms from(select height_cms,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select weight_pounds into w_ps from(select weight_pounds,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select date_of_birth into dob from(select date_of_birth,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select marital_status  into ms from(select marital_status,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select job_title into jt from(select job_title,row_number() over(order by person_id )as rn from persons)tmp where rn=ac;
    select department into dep from(select department,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select hiredate into hd from(select hiredate,row_number() over(order by person_id )as rn from persons)tmp where rn=ac;
    select aadhar_no into aad from(select aadhar_no,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;
    select status into sta from(select status,row_number() over(order by person_id)as rn from persons)tmp where rn=ac;

    insert into persons_data(Person_id,Firstname,Middlename,Lastname,Gender,Eyes,Color,Height_cms,weight_pounds,Date_of_birth,Marital_status,Job_Title,
    Department,hiredate,Aadhar_no,Status) values (id1,fn,mn,ln,gen,ey,col,h_cms,w_ps,dob,ms,jt,dep,hd,aad,sta);
    commit;

    id1:=id1+1;
    ac:=ac+1;
    a1:=a1+1;
    end loop;

    EXCEPTION  -- exception handlers begin
      WHEN OTHERS THEN  -- handles all other errors
              DBMS_OUTPUT.PUT_LINE (SQLCODE|| ' ' || SQLERRM);
    end;

Error report:

> ORA-06550: line 2, column 1:
>         PLS-00201: identifier 'A1' must be declared
>         ORA-06550: line 2, column 1:
>         PL/SQL: Statement ignored
>         ORA-06550: line 3, column 1:
>         PLS-00201: identifier 'ID1' must be declared
>         ORA-06550: line 3, column 1:
>         PL/SQL: Statement ignored
>         ORA-06550: line 4, column 1:
>         PLS-00201: identifier 'AC' must be declared
>         ORA-06550: line 4, column 1:
>         PL/SQL: Statement ignored
>         ORA-06550: line 8, column 7:
>         PLS-00201: identifier 'A1' must be declared
>         ORA-06550: line 8, column 1:
>         PL/SQL: Statement ignored
>         06550. 00000 -  "line %s, column %s:\n%s"
>         *Cause:    Usually a PL/SQL compilation error.
>         *Action:

Answer 1

If you as you said want to merely generate several duplicates for each row moved from persons table to person_data, here is one way to simplify your procedure.

create table persons_data as
  with dups as(
    select level
      from dual
     connect by level <= 10 -- how many duplicates for each row 
  )                         -- will be generated
  select rownum    -- for primary key column
       , p.<list of columns you want to move>
    from persons p
         cross join dups

Answer 2

The INTO in each of the SELECT statements is in the wrong place. It needs to be between the SELECT and FROM. The first statement, after reformatting for legibility, is:

select firstname
  from (select firstname,
               row_number() over (order by person_id) as rn
          from persons) tmp
  into fn
  where rn=ac;

It should be:

select firstname
  into fn
  from (select firstname,
               row_number() over (order by person_id) as rn
          from persons) tmp
  where rn=ac;

It looks like the rest of the statements are similar.

Best of luck.

Answer 3

Your are sure that ac variable is greather then cx?

for catch error try, oracle's begin exception block and use dbms_output.put_line for print the error message

BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';  -- might cause division-by-zero error
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
   COMMIT;
EXCEPTION  -- exception handlers begin
  WHEN OTHERS THEN  -- handles all other errors
          DBMS_OUTPUT.PUT_LINE (SQLCODE|| ' ' || SQLERRM);
END;  -- exception handlers and block end here