ORA-00957: duplicate column name while executing the procedure

Question

I am getting an error

ORA-00957: duplicate column name error while trying to create procedure

What is the problem ? How can I fix my query?

The procedure was created successfully. But when I try to execute the procedure, I got

ORA-00957: duplicate column name ORA-06512: at "SQL_BOTTZCVHAFEXQPWFMKGLKIGTC.SP_CRNA", line 91

When I execute each command in the query one-by-one, it executes properly. But executing it from the procedure call is not working.

CREATE OR replace PROCEDURE sp_crna
AS
  v_query1 CLOB;
BEGIN
  BEGIN
    v_query1:='DROP TABLE TOP20 PURGE';
    EXECUTE IMMEDIATE v_query1;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  BEGIN
    v_query1:=
    'CREATE TABLE top20 AS
    SELECT ROWNUM      AS rn,
          country
          || '' ''
          ||total_cases AS top20
    FROM   (
            SELECT   *
            FROM     corona_project
            ORDER BY total_cases DESC)
    WHERE  ROWNUM<21';
    EXECUTE IMMEDIATE v_query1;
  END;
  BEGIN
    v_query1:='DROP TABLE BOTTOM20 PURGE';
    EXECUTE IMMEDIATE v_query1;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  BEGIN
    v_query1:=
    'CREATE TABLE bottom20 AS
    SELECT ROWNUM        AS rn,
          country
          || '' ''
          ||total_cases AS BOTTOM20
    FROM   (SELECT *
            FROM   corona_project
            ORDER  BY total_cases)
    WHERE  ROWNUM < 21'; 
    EXECUTE IMMEDIATE v_query1;
  END;
  BEGIN
    v_query1:='DROP TABLE MIDDLE_ROW PURGE';
    EXECUTE IMMEDIATE v_query1;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  BEGIN
    v_query1:=
    'CREATE TABLE MIDDLE_ROW AS 
      SELECT ROWNUM               AS rn,
            SUM(total_cases)     AS total_cases,
            SUM(deaths)          deaths,
            SUM(recovered)       recovered,
            SUM(new_cases)       new_cases,
            SUM(new_deaths)      new_deaths,
            SUM(seriouscritical) seriouscritical
      FROM   corona_project
      GROUP  BY ROWNUM,
                total_cases,
                deaths,
                recovered,
                new_cases,
                new_deaths,
                seriouscritical';
    EXECUTE IMMEDIATE v_query1;
  END ;
  BEGIN
    v_query1:='DROP TABLE DISPLAY PURGE';
    EXECUTE IMMEDIATE v_query1;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  BEGIN
    v_query1:=
    'CREATE TABLE display AS
    SELECT T.*,
          M.*,
          B.*
    FROM   top20 T
          left join bottom20 B
                  ON T.rn = B.rn
          left join middle_row M
                  ON M.rn = T.rn'; 
    EXECUTE IMMEDIATE v_query1;
  END;
END sp_crna;
/

EXEC sp_crna; 

Answer 1

From my point of view, the whole procedure is wrong. In Oracle, we really, Really, REALLY rarely create tables dynamically. We do create them once (even if they are (global or private) temporary tables) and use them many times.

What you should do, in my opinion, is to create tables at SQL level and - using the stored procedure (if you want it) INSERT rows into those pre-created tables. When you don't need that data any more, TRUNCATE tables or DELETE their contents. According to what you posted, you'd delete tables at the beginning of that procedure.

Something like this:

create table top20
  (rn          number,
   top20       varchar2(100));

create table bottom20
  (rn          number,
   bottom20    varchar2(100));

create table middle_row
  (rn          number,
   total_cases number,
   deaths      number, 
   ...);

Now, create the procedure which will insert rows:

create or replace procedure p_rows as
begin
  delete from top20;
  delete from bottom20;
  delete from middle_row;

  insert into top20 (rn, top20) 
    select ...;

  ...
end;
/

Run it whenever you want:

begin
  p_rows;
end;
/

Instead of the display table, use a view:

create or replace view display as
  select t.rn,
         t.top20,
         b.bottom20,
         m.total_cases,
         m.deaths,
         m....
  from top20 t left join bottom20 b on b.rn = t.rn
               left join middle_row m on m.rn = t.rn;

---

Though, I'm not sure what is the rn column supposed to do in this context. ROWNUM maybe isn't the best column to be used for joins, so ... think about it (and its replacement).

---

As of error you got, this piece of code is wrong (in your procedure):

CREATE TABLE display AS
SELECT T.*,
       M.*,
       B.*
FROM   top20 T
       left join bottom20 B
              ON T.rn = B.rn
       left join middle_row M
              ON M.rn = T.rn

Why? Because those table share the same column (rn), and you can't have two (or more) columns with the same name in the same table. It means that you can't (and shouldn't) use asterisk, but name all columns - one-by-one - providing column aliases where necessary, e.g.

CREATE TABLE display AS
SELECT T.rn top_rn, t.top20,
       M.rn middle_rn, m.total_cases, m.deaths, m...,
       b.rn bottom_rn, b.bottom20
FROM   top20 T
       left join bottom20 B
              ON T.rn = B.rn
       left join middle_row M
              ON M.rn = T.rn