Using BLOB datatype in SQL

Question

I'm trying to create the following table. But the error message shown is "unrecognized token : "16M"

CREATE TABLE TOY_DTLS
(
    TOY_ID NUMBER(4) PRIMARY KEY,
    TOY_NAME VARCHAR2(30) NOT NULL UNIQUE,
    TOY_PRICE NUMBER(7,2) CHECK(TOY_PRICE>10),
    DISCOUNT_PRICE NUMBER(5,2) CHECK(DISCOUNT_PRICE<90),
    AGEGROUP NUMBER(3) DEFAULT 5,
    TOY_RATING NUMBER(1) CHECK(TOY_RATING>=1 AND TOY_RATING<=5),
    CATEGORY CHAR(1) CHECK(CATEGORY='I' OR CATEGORY='O' OR CATEGORY='B'),
    TOY_PIC BLOB(16M)
);

If attempting to store a picture of the toy take a look at this: http://www.dba-oracle.com/t_storing_insert_photo_pictures_tables.htm — FirebladeDan, Aug 13 '15 at 16:18

score 2 · Answer 1 · edited May 23 '17 at 11:51

When running this code in both 11gr2 and 12c the error is ORA-00907; there's no mention of unrecognised tokens.

SQL> CREATE TABLE TOY_DTLS
  2  (
  3      TOY_ID NUMBER(4) PRIMARY KEY,
  4      TOY_NAME VARCHAR2(30) NOT NULL UNIQUE,
  5      TOY_PRICE NUMBER(7,2) CHECK(TOY_PRICE>10),
  6      DISCOUNT_PRICE NUMBER(5,2) CHECK(DISCOUNT_PRICE<90),
  7      AGEGROUP NUMBER(3) DEFAULT 5,
  8      TOY_RATING NUMBER(1) CHECK(TOY_RATING>=1 AND TOY_RATING<=5),
  9      CATEGORY CHAR(1) CHECK(CATEGORY='I' OR CATEGORY='O' OR CATEGORY='B'),
 10      TOY_PIC BLOB(16M)
 11  );
    TOY_PIC BLOB(16M)
                *
ERROR at line 10:
ORA-00907: missing right parenthesis

The reason for this error is that the 16M is unnecessary. Looking at the data type documentation the syntax for large object datatypes is, in its entirety:

In other words, you're not able to specify a maximum size of a LOB.

If you remove the size of the BLOB then the table creates:

SQL> CREATE TABLE TOY_DTLS
  2  (
  3      TOY_ID NUMBER(4) PRIMARY KEY,
  4      TOY_NAME VARCHAR2(30) NOT NULL UNIQUE,
  5      TOY_PRICE NUMBER(7,2) CHECK(TOY_PRICE>10),
  6      DISCOUNT_PRICE NUMBER(5,2) CHECK(DISCOUNT_PRICE<90),
  7      AGEGROUP NUMBER(3) DEFAULT 5,
  8      TOY_RATING NUMBER(1) CHECK(TOY_RATING>=1 AND TOY_RATING<=5),
  9      CATEGORY CHAR(1) CHECK(CATEGORY='I' OR CATEGORY='O' OR CATEGORY='B'),
 10      TOY_PIC BLOB
 11  );

Table created.

There's a lot of discussion around whether images should be stored in the database or not. Before continuing down this path it's worth reading up on some of the information/opinions out there.

Using BLOB datatype in SQL

1 Answers1