CODEWITHSUNDEEP
sqloracle-databaseplsql
rcmv
rcmv

Reputation: 151

Oracle PL - SQL - "SQL command not properly ended" on an update statement

I've two update statements launched by an "Execute Immediate" function. One works ok but the other throws an "SQL command not properly ended" error.

Statement working fine:

 EXECUTE IMMEDIATE 'UPDATE myTable SET column_xpto = ' ||my_var|| ' WHERE myTable.name_table = '''||nameT||'''';

Statement NOT working:

EXECUTE IMMEDIATE 'UPDATE myTable SET column_X = ' ||my_other_var|| ' WHERE myTable.name_table = '''||nameT||'''';

The variable 'my_var' is a NUMBER type while the variable 'my_other_var' is a timestamp(6), initializated with CURRENT_TIMESTAMP.

desc myTable 
Name             Null     Type          
---------------- -------- ------------- 
NAME_TABLE       NOT NULL VARCHAR2(200) 
column_xpto               NUMBER              
column_X                  TIMESTAMP(6)

Does anyone know why the second statement gives an error?

Upvotes: 0

Views: 1383

Answers (2)

Exhausted
Exhausted

Reputation: 1885

you have to use like below 

EXECUTE IMMEDIATE 'UPDATE myTable SET column_X = ''' ||my_other_var|| ''' WHERE myTable.name_table = '''||nameT||'''';

since timestamp should be used as string literal.

You can either user bind variable also as Lalith said.

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

I would use bind variables like one below, with USING keyword.

EXECUTE IMMEDIATE 'UPDATE myTable SET column_xpto = :my_other_var WHERE myTable.name_table = :nameT' 
        USING my_other_var, nameT;

Upvotes: 3

Related Questions