unable to run sql file in ubuntu command line

Question

I have typed the following query in command line and it worked but when I try to run it through the command @..../filename.sql the first query works but the second doesn't...I can't figure out why. need help. Also the Error message I am getting reads SP2-0042 Unknown command ")" - rest of line ignored

here is the query

/** Query 1: works through terminal and by calling @.../filename.sql**/

CREATE TABLE CUSTOMER
( cID   NUMBER NOT NULL,
  firstName     VARCHAR2(50) NOT NULL,
  lastName  VARCHAR2(50) NOT NULL,
  streetAddress VARCHAR2(50),
    CONSTRAINT CUSTOMER_PK PRIMARY KEY (customerID) 
);

/**Query2: only works through terminal**/

CREATE TABLE ADDRESS
( 
  cID   NUMBER NOT NULL,
  city          VARCHAR2(50) NOT NULL,
  zipCode       VARCHAR2(50) NOT NULL,
    CONSTRAINT FK_CUSTOMER_ADDRESS FOREIGN KEY (cID) REFERENCES CUSTOMER(cID) ON UPDATE CASCADE

);

Can you elaborate on how it's not working? Does it produce an error message? Or is it just ignored? — Mureinik
– Mureinik, Commented Sep 20, 2015 at 20:53
the Error message I am getting reads SP2-0042 Unknown command ")" - rest of line ignored — Algo
– Algo, Commented Sep 20, 2015 at 20:55

Allan · Accepted Answer · 2015-09-20 20:58:11Z

1

SQL Plus doesn't like empty lines in the middle of DDL (for non-procedural objects). Remove the empty line before the closing parenthesis or run set sqlblanklines on before running that DDL.

answered Sep 20, 2015 at 20:58

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Algo Over a year ago

I tried set sqlblanklines on and also removed the blank line before the end parenthesis and it produced another error message this time ORA-00905: missing keyword

user330315 · Accepted Answer · 2015-09-20 21:16:26Z

1

Oracle does not support ON UPDATE CASCADE, it only supports ON DELETE CASCADE.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#CJAIHHGC

So apart from the empty line (that Allan already pointed out), you need to remove the ON UPDATE CASCADE for the foreign key.

answered Sep 20, 2015 at 21:16

user330315

Collectives™ on Stack Overflow

unable to run sql file in ubuntu command line

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related