When you build a table with dynamic SQL in a stored procedure, if you insert

Ask your teacher a question, if you want to set up a table and insert data into a stored procedure, what should be done, if the table inserted into the data is not established before inserting data, the stored procedure will be Error notification table does not exist, causing the stored procedure to fail, is there any workaround?  The reason why a table is not built outside the stored procedure is to execute the stored procedure periodically, if there is a drop in the table, it will cause the stored procedure to insert data error, so you want to automatically in the stored procedure to consider the establishment and processing of the table, insert data does not consider the use of dynamic SQL to insert, Because of this can not use plsql things (such as the Remote link optimization tips), ask how to deal with, thank you!  simply put, it is how to implement in a process or package, build a table and insert, and insert cannot be dynamic SQL, For example, if the table does not have a stored procedure, the response error is invalid.

   
 

    
  

  
CREATE OR REPLACE PROCEDURE y_p_test AS
    
  

  
BEGIN
    
  

  
 drop_table(‘y_test‘);
    
  

  
 EXECUTE IMMEDIATE ‘ create table y_test （id number, name varchar2(20)） ‘;
    
  

  

    
  

  
 FOR i IN (SELECT/*+driving_site(b) */ * FROM small_tab_local a, [email protected] b
    
  

  
 WHERE a.sub_id=b.sub_id AND a.acc_id=b.acc_id)
    
  

  
 LOOP
    
  

  
 INSERT INTO y_test VALUES i;
    
  

  
 END LOOP;
    
  

  
 COMMIT;
    
  

  
END;
   
 

a solution has been thought of, tested and effective, The idea is to save a drop table after the creation, and then use a save to insert the data, and then with a saved through the dynamic SQL in a way to call these 2 saved, because the dynamic SQL is executed to judge, so can be successfully executed for your reference

   
 

    
  

  
create or replace procedure drop_table(x varchar2) as
    
  

  
table1 number;
    
  

  
begin
    
  

  
select COUNT(*) INTO table1 from user_tables where table_name=upper(x);
    
  

  
if table1 > 0
    
  

  
 then execute immediate ‘drop table ‘||x;
    
  

  
 end if;
    
  

  
end;
    
  

  
/
    
  

  

    
  

  
CREATE OR REPLACE PROCEDURE y_p_test0
    
  

  
AS
    
  

  
BEGIN
    
  

  
 drop_table(‘y_test‘);
    
  

  
 EXECUTE IMMEDIATE ‘create table y_test as select * from fzt_dd where 1=2‘;
    
  

  
 END;
    
  

  
/
    
  

  

    
  

  
CREATE OR REPLACE PROCEDURE y_p_test AS
    
  

  
BEGIN
    
  

  
 FOR i IN (SELECT * FROM fzt_dd WHERE ROWNUM<10)
    
  

  
 LOOP
    
  

  
 INSERT INTO y_test VALUES i;
    
  

  
 END LOOP;
    
  

  
 COMMIT;
    
  

  
END;
    
  

  
/
    
  

  

    
  

  
CREATE OR REPLACE PROCEDURE y_p_test1 AS
    
  

  
BEGIN
    
  

  
EXECUTE IMMEDIATE ‘begin y_p_test0; end;‘;
    
  

  
EXECUTE IMMEDIATE ‘begin y_p_test; end;‘;
    
  

  
END;
    
  

  
/
   
 

From for notes (Wiz)

When you build a table with dynamic SQL in a stored procedure, if you insert