ORA-00908: missing NULL keyword in Oracle SQL (blob data type)

Question

I am encountering error in creating a table with constraints on 2 blob data types that should contain only json.

The error is ORA-00908: missing NULL keyword

Here is my script

CREATE TABLE TEST
  (
    LOG_ID NUMBER(19,0) NOT NULL,
    DATA_BEFORE BLOB NULL,
    DATA_AFTER BLOB NULL,
    CONSTRAINT LOG_ID_PK PRIMARY KEY (LOG_ID),
    CONSTRAINT DATA_BEFORE_JSON CHECK(DATA_BEFORE IS JSON FORMAT JSON)) LOB (DATA_BEFORE) STORE AS(STORAGE (NEXT 15M)),
    CONSTRAINT DATA_AFTER_JSON CHECK(DATA_AFTER IS JSON FORMAT JSON)) LOB (DATA_AFTER) STORE AS(STORAGE (NEXT 15M))
    );

Thank you very much.

Answer 1

You seem to be on a database whose version doesn't support what you are doing. For example, 11gR2:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> CREATE TABLE test
  2  (
  3     log_id       NUMBER (19, 0) CONSTRAINT pk_lid PRIMARY KEY,
  4     data_before  BLOB,
  5     data_after   BLOB,
  6     constraint   data_before_json CHECK (data_before IS json),
  7     constraint   data_after_json  check (data_after is json)
  8  )
  9     lob (data_before) store as(storage (next 15m)),
 10     lob (data_after)  store as(storage (next 15m));
   constraint   data_before_json CHECK (data_before IS json),
                                                       *
ERROR at line 6:
ORA-00908: missing NULL keyword

---

On 12.2:

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

SQL> CREATE TABLE test
  2  (
  3     log_id       NUMBER (19, 0) CONSTRAINT pk_lid PRIMARY KEY,
  4     data_before  BLOB,
  5     data_after   BLOB,
  6     constraint   data_before_json CHECK (data_before IS json),
  7     constraint   data_after_json  check (data_after is json)
  8  )
  9     lob (data_before) store as(storage (next 15m)),
 10     lob (data_after)  store as(storage (next 15m));

Table created.

SQL>

Pay attention to differences between your and my code.