Oracle ошибка 00933

I have the following code:

begin
for i in 1..2 loop
insert into dba_xy.despatch
select desp_id_seq.nextval,
      dbms_random.string('U',5),
      trunc(dbms_random.value(0000,9999)),
      prod_id from dba_xy.product 
              prod_name from dba_xy.product;        
end loop;

end;

When I run it, oracle gives me the following error message:

prod_name from dba_xy.product;
                        *

ERROR at line 8:
ORA-06550: line 8, column 29:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored

What I’m trying to do is link the existing prod_id and prod_name with new data inserted into the despatch table. I have set prod_name as a unique key in the product table and prod_id as the primary key and have set both as foreign key constraints in the despatch table. I need to include the prod_name into the despatch table to allow readers of the table to have more understanding of what prod_name needs to be found etc, rather than just giving the prod_id which will make no sense to them at all. But maybe I was thinking that I don’t need prod_id in the despatch table.
Please help.

After dropping the prod_id column from the despatch table, i altered my code:

begin
for i in 1..2 loop
insert into dba_xy.despatch
select desp_id_seq.nextval,
      dbms_random.string('U',5),
      trunc(dbms_random.value(0000,9999)),
              prod_name from dba_xy.product;        
end loop;

end;
/

and the following error message came up about the unique constraint:
begin
*
ERROR at line 1:
ORA-00001: unique constraint (DBA_XY.PROD_NAME_UC) violated
ORA-06512: at line 3

ORA-00933: SQL command not properly ended error occurs when an Oracle SQL command is ended with an inappropriate clause. The DML commands INSERT, UPDATE, DELETE, and SELECT should use oracle’s syntax. The error is thrown when a clause is added that does not normally come within the command of a SQL query. An SQL statement with a clause that isn’t allowed for that statement causes this error. The error SQL Error: ORA-00933: SQL command not properly ended may be fixed by updating your query and removing the clause that is creating the problem. This would be determined by the query type.

This error is caused by a SQL statement that has a clause that isn’t permitted in that statement. The query contains a JOIN keyword, such as INNER JOIN or LEFT JOIN. The query contains an ORDER BY that is not permitted in the query. In your query, you have a where clause that isn’t allowed. You may need to update the sql query using various ways such as sub queries or correlated queries to achieve the same functional results. To resolve the problem, eliminate any clauses that aren’t related to the sql query.

The Problem

If you add an incorrect clause to a query like INSERT, UPDATE, DELETE, or SELECT, the sql query will not be able to add the clause. The Oracle query will fail because it was unable to process the clause that is unrelated to the sql query. To get the same functionality, the clause should be deleted from the sql query or the query should be redone using an alternative technique.

update employee set deptid=1 orderby name;

Error

Error starting at line : 4 in command -
update employee set deptid=1 orderby name
Error at Command Line : 4 Column : 30
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

Solution 1

The INNER JOIN, OUTER JOIN, WHERE clause, or ORDER BY clause are not allowed in the INSERT statement. If any of the above clauses are included in the INSERT statement, the sql query will fail because it will encounter the irrelevant clause linked to the insert statement. To fix the error SQL Error: ORA-00933: SQL command not properly ended, delete the above clause from the INSERT statement.

Error 1

insert into employee values (2,'test',1) order by name;

Solution 1

insert into employee values (2,'test',1);

Error 2

insert into employee values (2,'test',1) where name ='sample';

Solution 2

insert into employee values (2,'test',1);

Error 3

insert into employee values (2,'test',1) join dept on dept.deptid=employee.deptid;

Solution 3

insert into employee values (2,'test',1);

Solution 2

If the update statement contains the irrelevant clauses such as ORDER BY, INNER JOIN, OUTTER JOIN the sql query will throw the error. The ORDER BY clause or joins should be removed to resolve the error SQL Error: ORA-00933: SQL command not properly ended.

Error 1

update employee set deptid=1 orderby name;

Solution 1

update employee set deptid=1;

Error 2

update employee
set employee.name = manager.name
inner join manager ON employee.managerid = manager.empid;

Solution 2

update employee set employee.name = (
  select name from manager
  where employee.managerid = manager.empid
);

Solution 3

If the DELETE statement includes an ORDER BY clause or an INNER JOIN, OUTER JOIN, the delete statement will fail. The table name and where condition for retrieving the record to delete should be included in the DELETE statement. If any additional clause is introduced to the delete statement, Oracle will thrown an error SQL Error: ORA-00933: SQL command not properly ended

Error 1

delete from employee where deptid=1 orderby name;

Solution 1

delete from employee where deptid=1;

Error 2

delete from employee
where employee.name = manager.name
inner join manager ON employee.managerid = manager.empid;

Solution 2

delete from employee where employee.name = (
  select name from manager
  where employee.managerid = manager.empid
);

Solution 4

If the SELECT query contains two or more tables, the tables are connected by using a comma as a separator and appending the table name. The error will be thrown if a comma is missing between table names. In the select query, a comma should be used between table names.

Error

select * from employee a employee b;

Solution

select * from employee a, employee b;

Solution 5

The select query includes the WHERE clause, ORDER BY clause, GROUP BY clause, and HAVING clause. The clauses must be placed in the proper order. If the clauses are added in a different order than expected, an error SQL Error: ORA-00933: SQL command not properly ended will be thrown.

Error

select * from employee b order by b.name where b.name='test';

Solution

select * from employee b where b.name='test' order by b.name ;

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'

ORA-00933 means that you use an unexpected word or phrase at the position where SQL parser knows what qualified keyword should be. Use cases that throw ORA-00933 may broadly vary. Here’re some cases of the error.

1. ALTER TABLESPACE
2. Create Table as Select (CTAS)
3. Substitution Variable

ALTER TABLESPACE

Tried to add a tempfile to a tablespace, but it failed with ORA-00933.

SQL> alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m auto extend on next 10m maxsize unlimited;
alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m auto extend on next 10m maxsize unlimited
                                                                                *
ERROR at line 1:
ORA-00933: SQL command not properly ended

Solution

Actually, the keyword is AUTOEXTEND, not AUTO EXTEND. We should correct the statement like this:

SQL> alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m autoextend on next 10m maxsize unlimited;

We solve it.

To correctly operate temporary tablespace, you may refer to the post: How to Alter Temporary Tablespace.

Create Table as Select (CTAS)

Tried to backup a table to a another tablespace by CTAS, but it failed with ORA-00933.

SQL> create table hr.employees_bak as select * from hr.employees tablespace example;
create table hr.employees_bak as select * from hr.employees tablespace example
                                                                       *
ERROR at line 1:
ORA-00933: SQL command not properly ended

Solution

This is because the TABLESPACE clause cannot be at the last, it should stick with CREATE TABLE clause

SQL> create table hr.employees_bak tablespace example as select * from hr.employees;

Substitution Variable

There’re hundreds of tables like pro001env, pro002env, … and so on.

Let’s see one of these tables.

SQL> desc pro220env
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER

When we want to use a substitution variable to execute the statement, it failed with ORA-00933.

SQL> select * from pro'&num'env;
Enter value for num: 220
old   1: select * from pro'&num'env
new   1: select * from pro'220'env
select * from pro'220'env
                 *
ERROR at line 1:
ORA-00933: SQL command not properly ended

As you can see, we use single quotes to isolate the variable, but it’s not working.

Solution

To use substitution variable to concatenate a string, you need a period (.) to separate the substitution variable from rest of characters.

SQL> select * from pro&num.env;
Enter value for num: 220
old   1: select * from pro&num.env
new   1: select * from pro220env

It works!

By the way, the use case of substitution variables is different from the scenarios of using bind variables.