OraFAQ Forum: SQL & PL/SQL » Why "SQL command not properly ended" error on one and not on the other?

Home » SQL & PL/SQL » SQL & PL/SQL » Why "SQL command not properly ended" error on one and not on the other? (Oracle SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why "SQL command not properly ended" error on one and not on the other? [message #634795]

Mon, 16 March 2015 05:31

Adam Brave
Messages: 12
Registered: March 2015
Location: Lisboa

Junior Member

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?

Report message to a moderator

Re: Why "SQL command not properly ended" error on one and not on the other? [message #634796 is a reply to message #634795]

Mon, 16 March 2015 05:47

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

For the same reason that the static versions would fail.
This is fine:

UPDATE mytable SET COLUMN = 6;

This isn't:

UPDATE mytable SET COLUMN2 = 16-MAR-15 11.40.25.537261 +01:00;

When you hard-code values in a SQL statement you've only actually got two datatypes available - number and varchar, and concating the variable value into the string gives the same result as hard-coding.
To get any other datatype you need to use the appropriate conversion function (to_timestamp for example) on a string.

If you had used bind variables you wouldn't have this problem.
If you'd just used static SQL referencing the variables directly you wouldn't have this problem - and there doesn't appear to be any need to use dynamic SQL in your examples.

Report message to a moderator

Re: Why "SQL command not properly ended" error on one and not on the other? [message #634797 is a reply to message #634796]

Mon, 16 March 2015 06:02

Adam Brave
Messages: 12
Registered: March 2015
Location: Lisboa

Junior Member

This solved the problem:

(...) SET column_X = ''' ||my_other_var|| ''' WHERE (...)

[Updated on: Mon, 16 March 2015 06:02]

Report message to a moderator

Re: Why "SQL command not properly ended" error on one and not on the other? [message #634799 is a reply to message #634797]

Mon, 16 March 2015 06:08

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

That's not a good idea. Should the sessions nls_timestamp_format be changed you could lose data.
If you really need dynamic sql use bind variables:

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

Report message to a moderator

Re: Why "SQL command not properly ended" error on one and not on the other? [message #634802 is a reply to message #634799]

Mon, 16 March 2015 06:32

Adam Brave
Messages: 12
Registered: March 2015
Location: Lisboa

Junior Member

Why? In case of the timestamp format be changed, the 'my_other_var' remains with the timestamp type, so wouldn't the variable store the date with the new format?
The variable nameT would also be affected?

Thanks in advance!

Report message to a moderator

Re: Why "SQL command not properly ended" error on one and not on the other? [message #634804 is a reply to message #634802]

Mon, 16 March 2015 06:49

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why what?

In addition, it is a very bad idea to use lower case and even more mixed case as column (or table) names.

Quote:

the variable store the date with the new format?

This is meaningless, a (real) date is stored in no (human) format.

Report message to a moderator

Previous Topic:	having clause is working before group by , why ?
Next Topic:	rank

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:20:47 CDT 2024