insert error in oracle statement inside procedure

Question

stmt_ins_bck:= 'insert into sii_bck_tab(id_bck_tab,nome_tab,id_bck_cfg_tab) VALUES (' || SII_BCK_TAB_ID_SEQ.CURRVAL || ',' || id_seq || ',' || id_fk || ')';
DBMS_OUTPUT.PUT_LINE(stmt_ins_bck);
EXECUTE IMMEDIATE stmt_ins_bck;

i get ORA-00984 error when i try to execute that insert, the way i see it everything seems fine and i cant figure out what i've done wrong.

Error says column is not allowed here.

Answer 1

The error suggests one your variables is a string, so you would have to enclose it in single quotes within the statement; and as those would be inside another string - the statement itself - they would need to be escaped.

From a comment it seems id_seq is a string and id_fk is a number, so something like:

stmt_ins_bck:= 'insert into sii_bck_tab(id_bck_tab,nome_tab,id_bck_cfg_tab) VALUES ('
  || SII_BCK_TAB_ID_SEQ.CURRVAL || ',''' || id_seq || ''',' || id_fk || ')';
-------------------------------------^^................^^ escaped single quotes
EXECUTE IMMEDIATE stmt_ins_bck;

But it's simpler and generally more efficient to use bind variables:

stmt_ins_bck:= 'insert into sii_bck_tab(id_bck_tab,nome_tab,id_bck_cfg_tab) VALUES (:v1,:v2,:v3)';
EXECUTE IMMEDIATE stmt_ins_bck USING SII_BCK_TAB_ID_SEQ.CURRVAL, id_seq, id_fk;

or possibly, as the sequence reference is reasonable in the fixed part of the statement:

stmt_ins_bck:= 'insert into sii_bck_tab(id_bck_tab,nome_tab,id_bck_cfg_tab) VALUES (SII_BCK_TAB_ID_SEQ.CURRVAL,:v1,:v2)';
EXECUTE IMMEDIATE stmt_ins_bck USING id_seq, id_fk;