Create table as select statement using procedure

Question

first of all i have 3 schema as follow ( MEDICINE, MEDICINE1 ,MEDICINE2) . MEDICINE and MEDICINE1 have same tables, MEDICINE have updated data and MEDICINE1 have old data. and i have MEDICINE2 with no tables so i want to create table in MEDICINE2 as select from MEDICINE where not in MEDICINE1.

Ex. create MEDICINE2.table1 as ( select * from MEDICINE.table1 minus select * MEDICINE1.table1 ) so if table1 in schema MEDICINE has data (1,2,3,4,5,6) and MEDICINE1 has (1,2,3,4) so table1 will create in MEDICINE2 with data (5,6)

because i have many tables, i create this procedure :

CREATE OR REPLACE PROCEDURE SYSTEM.create_table_from_schema IS
 TYPE own_array IS TABLE OF VARCHAR2(40)
    INDEX BY binary_integer;
 TYPE tab_array IS TABLE OF VARCHAR2(40)
    INDEX BY binary_integer;
  v_dml_str VARCHAR2 (400);
  v_own_array    own_array;
  v_tab_array    tab_array;
BEGIN

  SELECT owner,table_name,BULK COLLECT
    INTO v_own_array,v_tab_array
    from SYS.all_tables  
  where global_stats='YES'
  And owner = 'MEDICINE';


  FOR i IN v_tab_array.first..v_tab_array.last LOOP
    v_dml_str := 'Create table MEDICINE2.'
                 ||v_tab_array(i)||'as (select * from '||v_own_array(i)||'.'|| v_tab_array(i)
                 ||' minus select * from MEDICINE1.'|| v_tab_array(i)||' )'  ;             
    EXECUTE IMMEDIATE v_dml_str;
  END LOOP;
END;
/

but there is error will show

PROCEDURE SYSTEM.CREATE_TABLE_FROM_SCHEMA On line: 11 PL/SQL: ORA-00904: "BULK": invalid identifier

Are there errors in procedure ?

thanks and regard,

Answer 1

this is working fine

SET SERVEROUTPUT ON;
Declare
  v_dml_str VARCHAR2 (400);
  cursor c1 is
     SELECT owner,table_name
     from SYS.all_tables  
     where global_stats='YES'
     and owner = 'MEDICINE';
BEGIN

   FOR i in c1
   LOOP
    v_dml_str := 'Create table MEDICINE2.'
                 ||i.table_name||' as (select * from '||i.owner||'.'|| i.table_name
                 ||' minus select * from MEDICINE1.'|| i.table_name||' )'  ;             
    EXECUTE IMMEDIATE v_dml_str;
            END LOOP;

END;
/