Ora-00933: SQL Command Not Properly Ended

SQL Error: ORA-00933: SQL command not properly ended

Oracle does not allow joining tables in an UPDATE statement. You need to rewrite your statement with a co-related sub-select

Something like this:

UPDATE system_info
SET field_value = 'NewValue' 
WHERE field_desc IN (SELECT role_type 
                     FROM system_users 
                     WHERE user_name = 'uname')

For a complete description on the (valid) syntax of the UPDATE statement, please read the manual:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10008.htm#i2067715

SQL command not properly ended with WHERE statement

You probably want this:

select * from (
    SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)
) WHERE rownum = 1;

You have a syntax error in your query, because the ORDER BY comes after the WHERE clause. So, you'll have this after fixing the syntax error:

SELECT CITY, LENGTH(CITY) 
FROM STATION
WHERE ROWNUM = 1
ORDER BY LENGTH(CITY)

If you recall, the WHERE clause is evaluated before the ORDER BY clause.
Since, ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. the ROWNUM return values 1,2,3,... based on the order in which data from table station is fetched (could be in any order), which is probably not what you want.

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.

So, you query becomes this:

select * from (
    SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)
) WHERE rownum = 1;

In this, the rows are first sorted based on the length of content of CITY column inside the subquery, and then applies ROWNUM in the increasing order of the length of CITY column. So, now you can filter out the first row using ROWNUM = 1 predicate.

Alternatively, in Oracle database 12c and above, you can use the FETCH FIRST N ROWS ONLY clause to make your life easier (no subquery required).

SELECT CITY, LENGTH(CITY) 
FROM STATION
ORDER BY LENGTH(CITY)
FETCH FIRST 1 ROW ONLY;

This clause will be evaluated after the ordering is done and returns the top one row.

SQL error - ORA-00933: SQL command not properly ended

You could use:

select r.room_type_id as RoomType       -- rt alias changed to r to match group by
      ,count(r.no_of_reservations) as NoOfReservations
from Room_type rt                        -- explicit join
join Room r
  on rt.room_type_id = r.room_type_id
join Reservation res
  on r.room_id = res.room_id
where extract(month from res.check_in_date) = 3
  and extract(month from res.check_out_date) = 3
group by r.room_type_id       --swapped lines group by <=> order by
order by NoOfReservations;    --alias from SELECT

SQL Error: ORA-00933: SQL command not properly ended. When using a simple insert

Remove semi-colon, here:

string command = "insert into SOMETABLE (GROUPID, USERID, REMOVED) values 
('00000000000000000000000000000000', '00000000000000000000000000000000', 0);";
                                                                           ^
                                                                           |
                                                                        here

cx_Oracle ORA-00933 SQL command not properly ended

print (statmentApp)

gives:

SELECT TITLE,APP_URL from SOME.DATABASEWHERE UPPER(APP_URL) LIKE UPPER(:tc)AND ACTIVE_FLAG = :y

showing that the string concatenation worked too well. To retain necessary whitespace you'll find it easier to use triple quotes:

statmentApp = """SELECT TITLE,APP_URL from SOME.DATABASE
                 WHERE UPPER(APP_URL) LIKE UPPER(:tc)
                 AND ACTIVE_FLAG = :y"""

SQL Command Not Properly Ended for EXECUTE IMMEDIATE

Don't end your ALTER SYSTEM command with a semi-colon. The EXECUTE IMMEDIATE command will take care of that.

cursor cur_show_connections is
     SELECT    'ALTER SYSTEM DISCONNECT SESSION '''
               || SID
               || ','
               || SERIAL#
               || ''' IMMEDIATE' sqlstatement
     FROM v$session
     WHERE machine = 'XXXXX';

You're also concatenating your commands into a single EXECUTE IMMEDIATE statement. That won't work the way you're doing it: EXECUTE IMMEDIATE can only run one command or PL/SQL block at a time.

for rec_show_connections in cur_show_connections loop
    dbms_output.put_line(rec_show_connections.sqlstatement);
    EXECUTE IMMEDIATE rec_show_connections.sqlstatement;
end loop;

OR

sqlstr = 'BEGIN ';

for rec_show_connections in cur_show_connections loop
    sqlstr := sqlstr || dbms_output.put_line(rec_show_connections.sqlstatement) || '; ';
end loop;

sqlstr := sqlstr || ' END;'

EXECUTE IMMEDIATE sqlstr;

Depending on how many sessions you might need to disconnect, the first option is less likely to have issues with character string size limitations. PL/SQL has a 32K limitation that could affect how big a single PL/SQL concatenated block could get, potentially throwing an error if you tried to kill too many sessions at once.

SQL command not properly ended in Python OracleSQL

Query itself looks OK (as you said, it works in DBeaver). Maybe it is that Python doesn't "like" closing statement terminator (semi-colon at the very end of the query) - try to remove it.

---

Apart from that, I'd suggest you not to rely on Oracle's guessing date format. Instead of TO_DATE ('26-JUL-2021'), use TO_DATE ('26-JUL-2021', 'DD-MON-YYYY') (i.e. always provide appropriate format mask). Note that MON can be tricky if database doesn't speak English (for example, it would fail in my database which speaks Croatian) so - it is safer to use e.g. TO_DATE ('26.07.2021', 'DD.MM.YYYY')

---

Related Topics