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

Question

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?

Please edit your question and show the SQL after variable substitution. — Gordon Linoff
– Gordon Linoff, Commented Mar 16, 2015 at 10:29
... SET column_x = ''' ||my_other_var|| ''' ... worked for me. — Ponder Stibbons
– Ponder Stibbons, Commented Mar 16, 2015 at 10:41

Maheswaran Ravisankar · Accepted Answer · 2015-03-16 10:50:23Z

3

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;

answered Mar 16, 2015 at 10:50

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rcmv Over a year ago

I tried that, and it seems to work fine too. But what is the advantage? Beside the simplest sintaxe without all that commas LOL

Jeffrey Kemp Over a year ago

@AdamBrave, apart from the SQL injection vulnerability, another reason to use bind variables instead of concatenation is treatment of NULLs for numeric values: if you use simple concatenation you'll get a syntax error at runtime, whereas with bind variables it will at least run without error.

Exhausted · Accepted Answer · 2015-03-16 11:22:36Z

0

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.

edited Mar 16, 2015 at 11:22

answered Mar 16, 2015 at 10:40

Exhausted

1,8932 gold badges23 silver badges33 bronze badges

3 Comments

Lalit Kumar B Over a year ago

Better use bind variables.

rcmv Over a year ago

Why? Whats the advantage?

Exhausted Over a year ago

The bind variable will supply the value to pre-compiled, it will supply the value to precompiled one, wherears in above is to be re-parse the statement again on runtime.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related