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.

Kaushik Nayak · Accepted Answer · 2018-10-31 15:58:02Z

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?

answered Oct 31, 2018 at 15:58

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Sapo121 Over a year ago

do i need VALUES in the statement? im using a loop to fetch the names i need, so i really dont know any other way to do this process, thats why im trying to use variables.

Kaushik Nayak Over a year ago

@Sapo121 : Edit your question and show us the whole picture as to what you are trying to accomplish.

Sapo121 Over a year ago

I tried to explain what my final goal, but i was still far off, thats why i did not put all the info in

Sapo121 Over a year ago

i dont know what table i will need to insert into, so i dont know how many columns or which columns it will have

Kaushik Nayak Over a year ago

@Sapo121 : Well, it looks like you have source and target table names coming from another table's columns and dynamic insert seems inevitable, though I would call it a bad design. You may proceed with the option 1 I gave you and it will work unless there's a column order mismatch between the source and target tables, in which case you would have to bring the columns dynamically in the final query. Additionally, prefer implicit cursor for loops over open..fetch loops.

|

kanagaraj · Accepted Answer · 2018-10-31 15:57:45Z

1

Try this below block to pass tablename as variables:

declare
table_1 varchar2(10):='N_tab';
table_2 varchar2(10):='N_tab2';
test varchar2(1000);
begin

test:= 'INSERT all into ' || table_2 || ' SELECT * FROM ' ||table_1;
EXECUTE IMMEDIATE test;
dbms_output.put_line (test);

end;

answered Oct 31, 2018 at 15:57

kanagaraj

4421 gold badge4 silver badges8 bronze badges

Collectives™ on Stack Overflow

Using variables in oracle as table names for insert command oracle

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related