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

2
votes

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:
3
oracleplsql
First of all always post error message. Second, there are many places in your procedure where compile time error will be raised. Starting from show errors in the procedure definition, misplaced into clause in the select statements inside the else block, missing semicolons. You need to completely rewrite it. Because it looks like the entire procedure is an error. - Nick Krasnov
Well, show errors is added alongside of replace to print compilation errors. And I am trying to insert eg. firstname into the 'fn' variable. - Darla
show errors is SQL*PLUS command not the part of create procedure statement. - Nick Krasnov
If the persons_data table exists, the execute immediate will fail. If it doesn't exist, the procedure won't compile because it refers to it. - William Robertson
OK, will remove it and try. Thanks - Darla

3 Answers

1
votes

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
1
votes

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
1
votes

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.