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

possible duplicate of [ora-00933:SQL command not properly ended](http://stackoverflow.com/questions/1319992/ora-00933sql-command-not-properly-ended) — OMG Ponies, Jul 09 '10 at 16:51
This is a duplicate of [http://stackoverflow.com/questions/1319992/ora-00933sql-command-not-properly-ended](http://stackoverflow.com/questions/1319992/ora-00933sql-command-not-properly-ended) no? — Robert French, Aug 24 '09 at 05:22

score 0 · Answer 1 · answered Aug 24 '09 at 05:27

0

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 ?

answered Aug 24 '09 at 05:27

pero

4,169
26
27

Cartesian? Ummm what does that mean? – Aug 24 '09 at 05:31
This is a cross join or cartesian join. Look here: http://en.wikipedia.org/wiki/Join_(SQL)#Cross_join – pero Aug 24 '09 at 06:19
How would I go about loading data into a table, when one of the foreign keys are yet to have data loaded into it? – Aug 24 '09 at 06:44
DESP_ID ? What it's short name for ? You should probably first insert data into table that has DESP_ID as primary key. – pero Aug 24 '09 at 07:56

error ORA-06550 ORA-00933

1 Answers1