Using variables in oracle as table names for insert command oracle

Question

Trying to create a procedure that will either insert or update a certain table that its name is stored in another table with more info.

CREATE OR REPLACE PROCEDURE LIMPAR_TAB_proc IS

--stmt VARCHAR2(1000);
n_tab sii_bck_cfg_tab.nome_tab%type;
prefix sii_bck_cfg_tab.pref_tab_bck%type;
max_reg sii_bck_cfg_tab.max_reg_bck%type;
id_fk sii_bck_cfg_tab.id_bck_cfg_tab%type;
n_tab2 sii_bck_tab.nome_tab%type;
testes VARCHAR2(500);

CURSOR c1 IS
SELECT ID_BCK_CFG_TAB,Nome_tab, pref_tab_bck, max_reg_bck FROM 
sii_bck_cfg_tab WHERE desativado_em IS NULL OR desativado_em<=SYSDATE AND 
n_dias_reten>0 ORDER BY criado_em; 

CURSOR c2 IS 
SELECT sii_bck_tab.ID_BCK_CFG_TAB , sii_bck_tab.nome_tab from 
sii_bck_tab,sii_bck_cfg_tab WHERE 
sii_bck_cfg_tab.id_bck_cfg_tab=sii_bck_tab.id_bck_cfg_tab and dt_fecho is 
NULL ;

BEGIN                                  

OPEN c1;

LOOP
FETCH c1 INTO id_fk,n_tab,prefix,max_reg;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Nome Tabela = ' || id_fk ||' '|| n_tab ||' '|| prefix 
||' '|| max_reg); 
    OPEN c2;
      LOOP 
         FETCH c2 INTO id_fk, n_tab2;
         EXIT WHEN c2%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('chave aqui = ' || id_fk || n_tab2);
         IF c2%FOUND THEN

         testes:= 'INSERT INTO ' || n_tab2 || 'select * from ' || n_tab;
         EXECUTE IMMEDIATE testes;
         END IF;  
      END LOOP;
      CLOSE c2;

END LOOP;
CLOSE c1;

so i will try to explain my final objective, i want to go through my cursor1 and when i find a table that passes through the verification then i go into my cursor2. During my second loop i will want to verify if there is a table associated with a table on my cursor1 (not implemented ), then if i find one that is not associated i will need to create one with the same fields as the original(which is why im trying to save the table names in a variable). In case it exists and its dt_fim(date end) is null then i will need to insert all the data from the table from cursor1 (n_tab) into the table found on cursor2(n_tab2).

I will try to explain any doubts further, its still confusing to me, just getting started.

Thank you for any advice/help.

Answer 1

This is the right syntax, but not recommended for such a simple operation

testes:= 'INSERT INTO ' || n_tab2 ||' SELECT * FROM ' || n_tab;
EXECUTE IMMEDIATE testes;

Because It is preferable(and safer) to explicitly specify the column names in an insert, for which you need extra blocks if you want to do it dynamically.

INSERT INTO tab2(col1,col2,col3)  SELECT col1,col2,col3 FROM tab;

By the way, any reason why you've put table names in variable instead of doing a direct insert?