CODEWITHSUNDEEP
oracle-databaseplsqlconcatenationexecute-immediate
MsIsem
MsIsem

Reputation: 23

PL/SQL Concatenation

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

Upvotes: 1

Views: 211

Answers (1)

kfinity
kfinity

Reputation: 9091

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);

Upvotes: 4

Related Questions