Trailing semicolons
Semicolons are typically used in SQL scripts to separate SQL statements from one another, but they don't technically belong to the SQL statement and cause the ORA-00933: SQL command not properly ended error in the following example:
create table tq84_test_tab(a number);
begin
--
-- Note the trailing semicolon in the insert statement:
--
execute immediate '
insert into tq84_test_tab values(42);
';
end;
/
drop table tq84_test_tab;
Missing semicolon
In
PL/SQL, the semicolon
is important. Because it is missing in the following anonymous
PL/SQL block, it raises the ORA-00933 error:
declare
cnt number;
begin
select
count(*) into cnt
from
user_objects
dbms_output.put_line('Number of objects is: ' || cnt);
end;
/
UNION ALL combined with GROUP BY
An
ORA-00933 is also thrown when appling an
order by
clause to the first query of a statement that combines two queries with
union all
:
create table tq84_ora_933(gr varchar2(1), val number(4));
begin
insert into tq84_ora_933(gr, val) values ('A', 1000);
insert into tq84_ora_933(gr, val) values ('B', 500);
insert into tq84_ora_933(gr, val) values ('C', 1500);
commit;
end;
/
select gr , val from tq84_ora_933 order by val desc union all
select 'TOTAL', sum(val) from tq84_ora_933;
drop table tq84_ora_933;