PL/SQL Concatenation

Question

i have a procedure that have in entry two parameters CREATE_PARTITION( yearSource IN VARCHAR2 , yearDestination IN VARCHAR2 )

when i want to insert the yearSource concatenated with another string , nothing is inserted in the table

i declare the variable yearAA which take the yearSource and i concatenate it with a 'AA'

yearAA varchar2(30) := yearSource||'AA';

to insert it i use :

execute immediate ' INSERT INTO MOUADTEST2018 VALUES('||yearAA||')';

result : Nothing inserted and it shows this message ORA-00984: column not allowed here

score 4 · Accepted Answer · answered Jun 25 '18 at 15:25

You have to quote the value. As it is, if yearAA is 2018AA, your code gets interpreted as:

execute immediate 'INSERT INTO MOUADTEST2018 VALUES(2018AA)';

Which tries to run

INSERT INTO MOUADTEST2018 VALUES(2018AA)

Which throws an error because 2018AA isn't in quotes, so Oracle thinks it must be an identifier (like a column name).

You could change your code to

execute immediate 'INSERT INTO MOUADTEST2018 VALUES('''||yearAA||''')';

But this isn't a best practice, since it allows SQL injection. Instead, use bind variables.

execute immediate 'INSERT INTO MOUADTEST2018 VALUES(:1)' using yearAA;

Also, I don't think you need execute immediate, so you can just do:

insert into MOUADTEST2018 values(yearAA);

1 Answers1