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,

Jon wrote:

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/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Jon> Hi, I have a SQL script which contain SQL statements very
Jon> similar to the example below that create and modify a number of
Jon> tables. The SQL statement below is giving me a problem.

Jon> CREATE TABLE meta_packet ( id NUMBER, s9_pkt_id NUMBER(20), ts
Jon> TIMESTAMP(9), inbound NUMBER(1), ether_type NUMBER(5), ip_proto
Jon> NUMBER(3), accept NUMBER(3), mac_pair_id NUMBER, ether_body_id
Jon> NUMBER, ip_body_id NUMBER, conv_pair_id NUMBER, payload_id
Jon> NUMBER ); ALTER TABLE meta_packet ADD ( CONSTRAINT
Jon> meta_packet_pk PRIMARY KEY (id), CONSTRAINT mac_pair_fk FOREIGN
Jon> KEY (mac_pair_id) REFERENCES mac_addr_pair (id), CONSTRAINT
Jon> payload_fk FOREIGN KEY (payload_id) REFERENCES payload (id)
Jon> USING INDEX TABLESPACE indx);

Jon> Specifically, the problem is in the "ALTER TABLE meta_packet
Jon> ADD" portion of this statement. When I execute the ALTER TABLE
Jon> portion of the above statement from the SQL> prompt as follows,
Jon> I am getting this error:

SQL> ALTER TABLE meta_packet ADD
Jon> 2 ( 3 CONSTRAINT meta_packet_pk PRIMARY KEY (id), 4 CONSTRAINT
Jon> mac_pair_fk FOREIGN KEY (mac_pair_id) 5 REFERENCES mac_addr_pair
Jon> (id), 6 CONSTRAINT payload_fk FOREIGN KEY (payload_id) 7
Jon> REFERENCES payload (id) 8 USING INDEX TABLESPACE indx); USING
Jon> INDEX TABLESPACE indx) * ERROR at line 8: ORA-00907: missing
Jon> right parenthesis

Jon> Is there a syntax problem, are there too many
Jon> CONSTRAINT/REFERENCES statements or are the
Jon> CONSTRAINT/REFERENCES statements in the wrong order? I am at a
Jon> loss to understand the problem. Any ideas?

Jon> Thanks,

Missing comma at end of line 7, before line 8?

Tim
--
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you
really need to send mail, you should be able to work it out!