ora-00933:SQL command not properly ended

Question

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

Answer 1

Read up on Normalisation.

Your PRODUCT table should have the id as the primary key and the name as the attribute. In this case, having a unique constraint on the product name is also appropriate. Note that Oracle can use a non-unique index to enforce a unique constraint, but generally you'll have a unique index on the column you want to be unique.

Your DESPATCH table should NOT have the product name. If you include the name there, you'll have a nightmare of a time when a product changes it's name because someone mis-spelt it or marketing decides to change its mind.

DESPATCH should have the product_id and a foreign key constraint to the PRODUCT table.

You can create a DESPATCH_VW that joins the DESPATCH table to the PRODUCT table to obtain the product name.

Answer 2

Could you be getting the unique constraint violation because you are inserting the same rows twice? Is "i" supposed to be used in the where clause of the insert statement or do you really want the rows inserted twice?

Your first statement has two FROM clauses, which is why you are getting a syntax error.

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;

Answer 3

Your ORA-00933 error is due to an incorrectly formatted SELECT statement:

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;

...when it should be:

SELECT DESP_ID_SEQ.nextval,
       DBMS_RANDOM.string('U',5),
       TRUNC(DBMS_RANDOM.value(0000,9999)),
       t.prod_id,
       t.prod_name 
  FROM dba_xy.product t;

You were missing the comma to separate the prod_id and prod_name columns, and additionally had a redundant FROM dba_xy.product declaration in the wrong location.

That said, the dba_xy.despatch table should only contain the prod_id. If you need to provide a human readable version of the data, I recommend you construct a view. Example:

CREATE VIEW despatch_vw AS
SELECT t.prod_id,
       p.prod_name
  FROM dba_xy.despatch t
  JOIN dba_xy.product p ON p.prod_id = t.prod_id