insert error in oracle statement inside procedure

1255 views sql
8

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.

answered question

What data types are the variables you are concatenating into the statement?

@AlexPoole id_seq is varchar2 and id_fk sii_bck_cfg_tab.id_bck_cfg_tab%type; but i have also tried inserting id_fk as a number which is what its declared as.

1 Answer

0

The error suggests one your variables is a string, so you would have to enclose it in single quotes within the statement.

But it's simpler 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 possible, as the sequence reference is reasonable in the fixed part fo 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;

posted this

Have an answer?

JD

Please login first before posting an answer.