Search code examples
oracleparentheses

What's causing this Missing Right Parenthesis Oracle Error After Adding Tablespace References?

Sorry for adding this to the "missing right parenthesis" pool on the site but I poured through those and these were the only two that seemed similar to my code and neither helped me fix my issue although I do think they helped get me headed in the right direction. ORA-00907 when trying to create a CHECK constraint and Oracle: Make a composite Key containing three Foregin keys

I'm also not a database person but I have no choice but to take care of this so any help would be appreciated. I think my issue seems to really have something to do with where "Constraint" is located or most likely where or how I have added our Tablespace references. Adding Tablespace references was the only thing I changed in the script from the original provided by the app company.

This is one of the items from the original script provided by the company (edited down for this post)(all the others are similar and having the same error at the same spot):

CREATE TABLE my_sys_cache_entry
(
            myPVStream BLOB,
            myEntrySource VARCHAR2 (32) 
,  CONSTRAINT my_sys_cache_entry_PK PRIMARY KEY (myInsKey)
)
LOB(myPVStream) STORE AS (DISABLE STORAGE IN ROW);

This is the last change I made to it to try to get it to work with our Tablespace references added:

CREATE TABLE my_sys_cache_entry
(
            myPVStream BLOB,
            myEntrySource VARCHAR2 (32),CONSTRAINT my_sys_cache_entry_PK PRIMARY KEY (myInsKey) TABLESPACE "MY_DYN_IDX_01"
) TABLESPACE "MY_DYN_DAT_01" 
            LOB(myPVStream) STORE AS (TABLESPACE "MY_DYN_LOB_01" DISABLE STORAGE IN ROW);

This is the error I'm getting:

myEntrySource VARCHAR2 (32),CONSTRAINT my_sys_cache_entry_PK PRIMARY KEY (myInsKey) TABLESPACE "MY_DYN_IDX_01"
                                                                                                *
ERROR at line 23:
ORA-00907: missing right parenthesis

I've tried several ways to get this to work. Originally CONSTRAINT was on a new line under where it is now, the last ) was on a line below that (see example from original). We still got the error. I put the ) on the same line after the first TABLESPACE and we still got the error. I move everything from CONSTRAINT onward to one line at the end and still got the error. I can't recall if I tried it with the , removed before the CONSTRAINT. Any thoughts?

I can't test it without the Tablespace references.

Solution

  • add "using index" in there:

    CONSTRAINT my_sys_cache_entry_PK PRIMARY KEY (myInsKey)
 USING INDEX TABLESPACE "MY_DYN_IDX_01"

    the LOB part is fine. eg

    SQL> CREATE TABLE my_sys_cache_entry
  2  (
  3              myInsKey number, myPVStream BLOB,
  4              myEntrySource VARCHAR2 (32),
  5              CONSTRAINT my_sys_cache_entry_PK PRIMARY KEY (myInsKey) using index TABLESPACE users
  6  ) TABLESPACE users
  7              LOB(myPVStream) STORE AS (TABLESPACE users DISABLE STORAGE IN ROW);

Table created.