Re: Missing right parenthesis

Jon wrote:

> Hi,
>
> I have a SQL script which contain SQL statements very similar to the
> example below that create and modify a number of tables. The SQL
> statement below is giving me a problem.
>
> CREATE TABLE meta_packet
> (
> id NUMBER,
> s9_pkt_id NUMBER(20),
> ts TIMESTAMP(9),
> inbound NUMBER(1),
> ether_type NUMBER(5),
> ip_proto NUMBER(3),
> accept NUMBER(3),
> mac_pair_id NUMBER,
> ether_body_id NUMBER,
> ip_body_id NUMBER,
> conv_pair_id NUMBER,
> payload_id NUMBER
> );
> ALTER TABLE meta_packet ADD
> (
> CONSTRAINT meta_packet_pk PRIMARY KEY (id),
> CONSTRAINT mac_pair_fk FOREIGN KEY (mac_pair_id)
> REFERENCES mac_addr_pair (id),
> CONSTRAINT payload_fk FOREIGN KEY (payload_id)
> REFERENCES payload (id)
> USING INDEX TABLESPACE indx);
>
> Specifically, the problem is in the "ALTER TABLE meta_packet ADD"
> portion of this statement. When I execute the ALTER TABLE portion of
> the above statement from the SQL> prompt as follows, I am getting this
> error:
>
> SQL> ALTER TABLE meta_packet ADD
> 2 (
> 3 CONSTRAINT meta_packet_pk PRIMARY KEY (id),
> 4 CONSTRAINT mac_pair_fk FOREIGN KEY (mac_pair_id)
> 5 REFERENCES mac_addr_pair (id),
> 6 CONSTRAINT payload_fk FOREIGN KEY (payload_id)
> 7 REFERENCES payload (id)
> 8 USING INDEX TABLESPACE indx);
> USING INDEX TABLESPACE indx)
> *
> ERROR at line 8:
> ORA-00907: missing right parenthesis
>
> Is there a syntax problem, are there too many CONSTRAINT/REFERENCES
> statements or are the CONSTRAINT/REFERENCES statements in the wrong
> order? I am at a loss to understand the problem. Any ideas?
>
> Thanks,

Put each ALTER TABLE into a separate statement. It improves maintainability as well as giving you
valid syntax.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)