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
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