PL/SQL Execute immediate create table and insert data into it

Question

Let's have a look on my source code:

CREATE OR REPLACE PROCEDURE MAKE_COPY_OF_CLASSROOMS AUTHID CURRENT_USER AS

TYPE classrooms_table_type IS TABLE OF classrooms%ROWTYPE INDEX BY PLS_INTEGER;
classrooms_backup classrooms_table_type;

CURSOR classrooms_cursor IS
SELECT *
FROM classrooms
WHERE year = 1
ORDER BY name;

v_rowcnt PLS_INTEGER := 0;

BEGIN
    OPEN classrooms_cursor;
        FETCH classrooms_cursor
         BULK COLLECT INTO classrooms_backup;
    CLOSE classrooms_cursor;    

    EXECUTE IMMEDIATE 'CREATE TABLE classrooms_copy AS (SELECT * FROM classrooms WHERE 1 = 2)';
--COPY ALL STORED DATA FROM classrooms_backup TO classrooms_copy

END MAKE_COPY_OF_classrooms;

I'm stucked for hours on trying to insert data from "classrooms_backup" into the table "classrooms_copy", which is created by EXECUTE IMMEDIATE command. It's necessary to create table "classrooms_copy" via EXECUTE IMMEDIATE command. I tried to create another EXECUTE command with for loop in it:

EXECUTE IMMEDIATE 'FOR i IN classrooms_backup.FIRST..classrooms_backup.LAST LOOP
  INSERT INTO classrooms_copy(id,room_id,year,name) 
  VALUES(classrooms_backup(i).id,classrooms_backup(i).room_id,classrooms_backup(i).year,classrooms_backup(i).name);
  END LOOP;';

But it's road to the hell. I'm retrieving an invalid SQL statement error. Thanks for your help!

Answer 1

There's no need for much PL/SQL here. Also, try to avoid the keyword CURSOR - there's almost always a better way to do it.

create or replace procedure make_copy_of_classrooms authid current_user as
begin
    execute immediate '
        create table classrooms_copy as
        select *
        from classrooms
        where year = 1
        order by name
    ';
end make_copy_of_classrooms;
/