error ORA-06550 ORA-00933.

Question

I have a sales table:

    Name         Null?      Type  
SALE_ID         NOT NULL  NUMBER(4)  
SALE_DATE                 DATE  
NO_OF_PRODS               NUMBER(4)  
PROD_ID         NOT NULL  NUMBER(4)  
CUST_ID         NOT NULL  NUMBER(4)  
DESP_ID         NOT NULL  NUMBER(4)  
SALE_RECEIPT    NOT NULL  NUMBER(5)

I am trying to insert randomly generated data into the sales table. I am using iSQL plus for oracle. This is just test data that I have to create. I run the following script to generate the data:

begin
insert into sales
select sale_id_seq.nextval,
       sysdate,
       trunc(dbms_random.value(000,999)),
       p.prod_id, c.cust_id 
       FROM dba_xy.product p, dba_xy.customer c,
       desp_id_seq.nextval,
           trunc(dbms_random.value(0000,9999));

end; /

But when I do, the following error message appears:

trunc(dbms_random.value(0000,9999));
                *

ERROR at line 9: ORA-06550: line 9, column 21: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 2, column 2: PL/SQL: SQL Statement ignored.

What have I done wrong?

I just realised that the DESP_ID is a foreign key within the sales table, but currently the despatch table is empty and when I try to insert data into either tables, I'm not able to cause one table needs the data from the other table. I end up getting this error message:

PL/SQL: ORA-02289: sequence does not exist

Answer 1

This is a duplicate of http://stackoverflow.com/questions/1319992/ora-00933sql-command-not-properly-ended no?

Answer 2

You cannot select FROM

FROM dba_xy.product p, dba_xy.customer c,
   desp_id_seq.nextval,
       trunc(dbms_random.value(0000,9999));

Try:

insert into sales
(select 
   sale_id_seq.nextval,
   sysdate,
   trunc(dbms_random.value(000,999)),
   p.prod_id, 
   c.cust_id, 
   desp_id_seq.nextval,
   trunc(dbms_random.value(0000,9999))
   FROM dba_xy.product p, dba_xy.customer c;

BTW, are you sure that you want an Cartesian product here, maybe some join is missed ?