Why does my query produce error "ORA-00933: SQL command not properly ended"?

Question

My query:

CREATE VIEW cambiodatos AS 
SELECT 
    a.last_name||','||a.first_name AS "Nombre", 
    a.salary AS "Salario", 
    b.name AS "Nombre Departamento", 
    c.name AS "Nombre de Region"
FROM 
    s_emp a, s_dept b, s_region c
WHERE 
    a.dept_id = b.id AND b.region_id = c.id

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri'||','||first_name = 'Ben'

The error:

ORA-00933: SQL command not properly ended

score 2 · Answer 1 · answered Apr 20 '09 at 09:42

The exact reason for why you're getting that error is that you have this WHERE-clause:

last_name = 'Biri'||','||first_name = 'Ben'

This is not legal syntax.

This would be:

last_name = 'Biri' AND first_name = 'Ben'

Or something like this:

name = 'Biri'||','||'Ben'

but then you could just write it like this:

name = 'Biri,Ben'

The problem is that it looks to me that you're using the second || there as an AND clause, but that doesn't fit in with the comma you're trying to add.

Perhaps you're trying to execute this?

last_name || ',' || first_name = 'Biri,Ben'

In any case, as others have pointed out, if you fix that syntax problem, you'll just get other error message about missing column names.

Quassnoi · Answer 2 · 2009-04-20T09:35:53.093

First, separate you queries with a semicolon and fix your SET conditions:

CREATE VIEW cambiodatos AS 
SELECT 
    a.last_name||','||a.first_name AS "Nombre", 
    a.salary AS "Salario", 
    b.name AS "Nombre Departamento", 
    c.name AS "Nombre de Region"
FROM 
    s_emp a, s_dept b, s_region c
WHERE 
    a.dept_id = b.id AND b.region_id = c.id;

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri'
     AND first_name = 'Ben'

That's the reason of your error ORA-00933

Second, your UPDATE statement will fail, as the view you created does not contain field name.

This query will compile:

UPDATE 
     cambiodatos 
SET 
     "Nombre de Region" = 'North America'
WHERE 
     "Nombre" = 'Biri, Ben'

, but most probably will fail as s_region is not key-preserved in this view.

To update, use this instead:

MERGE
INTO    s_region c
USING   (
        SELECT  b.region_id
        FROM    s_emp a, s_dept b
        WHERE   a.last_name || ',' || a.first_name = 'Biri, Ben'
                AND b.id = a.dept_id
        ) q
ON      c.id = q.region_id
WHEN MATCHED THEN
UPDATE
SET     c.name = 'North America'

score 0 · Answer 3 · answered Apr 18 '09 at 20:04

0

Looks like you want an AND in the update

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri' AND first_name = 'Ben'

answered Apr 18 '09 at 20:04

Johnno Nolan

29,228
19
111
160

Neither last_name or first_name are in the view as columns. – WW. Apr 20 '09 at 07:05
Correct, but fixing that problem will get rid of the error message in question, but of course it will just produce different error messages afterwards. – Lasse V. Karlsen Apr 20 '09 at 09:43

score 0 · Answer 4 · answered Apr 18 '09 at 20:05

0

I haven't used Oracle at all during the last 7 years or so but don't you need a ; at the end of the statements?

answered Apr 18 '09 at 20:05

Fredrik

5,759
2
26
32

No, you don't need a semicolon at the end of Oracle SQL statements, but you can use it to tell sqlplus that the statement is complete and to send it. But that can also happen with `/` on a line by itself after the SQL statement. Without either, the statement would not be sent to the DB server, so no error about command ending would be generated. – Shannon Severance Aug 18 '11 at 20:24
@Shannon Severance Of course, you're correct but that was sort of what I was trying to explain to him (even if Quassnoi did it better). As Oracle told him the statement wasn't ended correctly I assumed he was sending it from sqlplus. It is not an oracle specific thing, most databases work the same. ';' is almost always valid but most have alternative endings as well. In this case I am pretty sure the entire statement (with CREATE VIEW and UPDATE was processed and when it saw the UPDATE keyword it barfed on it because it was clear that the CREATE VIEW statement must have been incorrectly ended. – Fredrik Aug 19 '11 at 07:26
You read closer than I did. SO won't let me correct my vote. Sorry about that. – Shannon Severance Aug 19 '11 at 13:47

score 0 · Answer 5 · edited May 23 '17 at 11:51

First, I think that your UPDATE command is poorly formatted. Second, you are using fields from the underlying tables instead of the view that you are running the update against. Also, I don't think you can update a view that based on a join. See the response to this question. If you could might look like this.

UPDATE 
     cambiodatos 
SET 
     [Nombre de Region] = 'North America'
WHERE 
     Nombre = 'Biro, Ben'

score -1 · Answer 6 · answered Apr 20 '09 at 03:42

CREATE VIEW cambiodatos AS SELECT a.last_name||','||a.first_name AS "Nombre", a.salary AS "Salario", b.name AS "Nombre Departamento", c.name AS "Nombre de Region" FROM s_emp a, s_dept b, s_region c WHERE a.dept_id = b.id AND b.region_id = c.id ; /* missing semicolon? */

UPDATE cambiodatos SET name = 'North America' WHERE last_name = 'Biri'||','||first_name = 'Ben' /* missing an And last_name = <> AND first_name = <> */

Why does my query produce error "ORA-00933: SQL command not properly ended"?

6 Answers6