Ошибка oracle ora 00933 sql command not properly ended

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

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.