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
Best Answer
Your ORA-00933 error is due to an incorrectly formatted SELECT statement:
...when it should be:
You were missing the comma to separate the
prod_id
andprod_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: