SQL Error: ORA-00933: SQL command not properly ended in Oracle Update query

Question

I have SYSTEM_SQL_CHECK table in which i have saved sql in CHECK_SQL column. This column is Varchar data type. Now i want to update particular sql.I have written below update sql query but it gives an error SQL Error: ORA-00933: SQL command not properly ended. I also tried to query with double quote but then it gives an error as SQL Error: ORA-00972: identifier is too long.

    Update RATOR_MONITORING_CONFIGURATION.SYSTEM_SQL_CHECK SET CHECK_SQL = 'select count(*) as CNT from O2_SDR_Header
where id = (select max(id) from O2_SDR_Header where id > 2012000000000000 and sp_id = 'SP602') 
And sp_id = 'SP602' and FILE_CREATED_DATE > (SYSTIMESTAMP - INTERVAL '2' HOUR)'
WHERE SYSTEM_SQL_CHECK_ID = 604;

String literals need to be enclosed in single quotes in SQL. Please read the manual (the chapter "Basic Elements of Oracle SQL") — user330315
– user330315, Commented Jun 16, 2015 at 13:43

Moudiz · Accepted Answer · 2015-06-16 13:51:24Z

3

This code should be like this : sp_id = 'SP602' to sp_id = ''SP602''

and this '2' to ''2''

your final code should be like this

 Update RATOR_MONITORING_CONFIGURATION.SYSTEM_SQL_CHECK SET CHECK_SQL = 'select count(*) as CNT from O2_SDR_Header
where id = (select max(id) from O2_SDR_Header where id > 2012000000000000 and sp_id = ''SP602'') 
And sp_id = ''SP602'' and FILE_CREATED_DATE > (SYSTIMESTAMP - INTERVAL ''2'' HOUR)'
WHERE SYSTEM_SQL_CHECK_ID = 604;

answered Jun 16, 2015 at 13:51

Moudiz

7,42723 gold badges87 silver badges166 bronze badges

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

Comments

davegreen100 · Accepted Answer · 2015-06-16 13:44:29Z

0

you need to escape you single quote, as below

Update RATOR_MONITORING_CONFIGURATION.SYSTEM_SQL_CHECK SET CHECK_SQL = "select count(*) as CNT from O2_SDR_Header
where id = (select max(id) from O2_SDR_Header where id > 2012000000000000 and sp_id = ''SP602'') 
And sp_id = ''SP602'' and FILE_CREATED_DATE > (SYSTIMESTAMP - INTERVAL '2' HOUR)"
WHERE SYSTEM_SQL_CHECK_ID = 604;

answered Jun 16, 2015 at 13:44

davegreen100

2,1253 gold badges15 silver badges25 bronze badges

6 Comments

Marcus Over a year ago

As i mentioned in my query when i tried to use double quote i am getting an error as SQL Error: ORA-00972: identifier is too long

Alex Poole Over a year ago

@Rahul - it isn't a double-quote (") around the text literals, it's two single quotes ('''). Very different. Also consider the alternative quoting mechanism for text literals. You need to escape the quotes around the '2' interval value too.

Marcus Over a year ago

Ohh sorry Alex but i tried the above query also and it gives me the same identifier error.

Moudiz Over a year ago

@Rahul the 2 should be fixed also add ''2''

user330315 Over a year ago

This is still wrong. The whole literal needs to be put in single quotes, not double quotes.

|

Collectives™ on Stack Overflow

SQL Error: ORA-00933: SQL command not properly ended in Oracle Update query

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related