How to set the column size, Seth? create table temp_photo ( ID NUMBER(3) NOT NULL, PHOTO_NAME VARCHAR2(50), PHOTO BLOB(100M) ) Error at line 1 ORA-00907: missing right parenthesis create table temp_photo ( ID NUMBER(3) NOT NULL, PHOTO_NAME VARCHAR2(50), PHOTO BLOB(8192) ) Error at line 9 ORA-00907: missing right parenthesis Or have I misunderstood you? Regards Kim Berg Hansen http://dspsd.blogspot.com kibeha@xxxxxxxxx @kibeha On Wed, Sep 17, 2014 at 11:29 PM, Seth Miller <sethmiller.sm@xxxxxxxxx> wrote: > I must be missing something but I would just set the column size. > > BLOB(100M) > > Seth Miller > On Sep 17, 2014 11:54 AM, "David Fitzjarrell" < > dmarc-noreply@xxxxxxxxxxxxx> wrote: > >> Thanks for doing this; I had time to test the one scenario but not others. >> >> David Fitzjarrell >> >> Principal author, "Oracle Exadata Survival Guide" >> >> >> On Wednesday, September 17, 2014 10:33 AM, Kim Berg Hansen < >> kibeha@xxxxxxxxx> wrote: >> >> >> DBMS_LOB.GETLENGTH counts as a userdefined function and won't work in a >> check constraint, true. >> But deterministic builtin functions will, and that seems to work with >> both CLOBS and BLOBS. >> A quick test (sql-developer script output): >> >> >> > create table temp_text >> ( >> ID NUMBER(3) NOT NULL, >> TEXT_NAME VARCHAR2(50), >> TEXT CLOB constraint text_max_8192 check(length(text) < 8192) >> ) >> table TEMP_TEXT created. >> > declare >> l_text clob; >> begin >> dbms_lob.createtemporary(l_text,false); >> for i in 1..4 loop >> dbms_lob.append(l_text,lpad('X',1000,'X')); >> end loop; >> insert into temp_text values ( >> 1, 'Clob length 4000', l_text >> ); >> commit; >> end; >> anonymous block completed >> > declare >> l_text clob; >> begin >> dbms_lob.createtemporary(l_text,false); >> for i in 1..8 loop >> dbms_lob.append(l_text,lpad('X',1000,'X')); >> end loop; >> insert into temp_text values ( >> 1, 'Clob length 8000', l_text >> ); >> commit; >> end; >> anonymous block completed >> > declare >> l_text clob; >> begin >> dbms_lob.createtemporary(l_text,false); >> for i in 1..16 loop >> dbms_lob.append(l_text,lpad('X',1000,'X')); >> end loop; >> insert into temp_text values ( >> 1, 'Clob length 16000', l_text >> ); >> commit; >> end; >> >> Error starting at line : 41 in command - >> declare >> l_text clob; >> begin >> dbms_lob.createtemporary(l_text,false); >> for i in 1..16 loop >> dbms_lob.append(l_text,lpad('X',1000,'X')); >> end loop; >> insert into temp_text values ( >> 1, 'Clob length 16000', l_text >> ); >> commit; >> end; >> Error report - >> ORA-02290: check constraint (HR.TEXT_MAX_8192) violated >> ORA-06512: at line 8 >> 02290. 00000 - "check constraint (%s.%s) violated" >> *Cause: The values being inserted do not satisfy the named check >> >> *Action: do not insert values that violate the constraint. >> > select id, text_name, length(text) ln from temp_text >> ID TEXT_NAME LN >> ---------- -------------------------------------------------- ---------- >> 1 Clob length 4000 4000 >> 1 Clob length 8000 8000 >> >> > create table temp_photo >> ( >> ID NUMBER(3) NOT NULL, >> PHOTO_NAME VARCHAR2(50), >> PHOTO BLOB constraint photo_max_8192 check(length(photo) < 8192) >> ) >> table TEMP_PHOTO created. >> > declare >> l_photo blob; >> begin >> dbms_lob.createtemporary(l_photo,false); >> for i in 1..4 loop >> dbms_lob.append(l_photo,hextoraw(lpad('F0',2000,'F0'))); >> end loop; >> insert into temp_photo values ( >> 1, 'Blob length 4000', l_photo >> ); >> commit; >> end; >> anonymous block completed >> > declare >> l_photo blob; >> begin >> dbms_lob.createtemporary(l_photo,false); >> for i in 1..8 loop >> dbms_lob.append(l_photo,hextoraw(lpad('F0',2000,'F0'))); >> end loop; >> insert into temp_photo values ( >> 1, 'Blob length 8000', l_photo >> ); >> commit; >> end; >> anonymous block completed >> > declare >> l_photo blob; >> begin >> dbms_lob.createtemporary(l_photo,false); >> for i in 1..16 loop >> dbms_lob.append(l_photo,hextoraw(lpad('F0',2000,'F0'))); >> end loop; >> insert into temp_photo values ( >> 1, 'Blob length 16000', l_photo >> ); >> commit; >> end; >> >> Error starting at line : 92 in command - >> declare >> l_photo blob; >> begin >> dbms_lob.createtemporary(l_photo,false); >> for i in 1..16 loop >> dbms_lob.append(l_photo,hextoraw(lpad('F0',2000,'F0'))); >> end loop; >> insert into temp_photo values ( >> 1, 'Blob length 16000', l_photo >> ); >> commit; >> end; >> Error report - >> ORA-02290: check constraint (HR.PHOTO_MAX_8192) violated >> ORA-06512: at line 8 >> 02290. 00000 - "check constraint (%s.%s) violated" >> *Cause: The values being inserted do not satisfy the named check >> >> *Action: do not insert values that violate the constraint. >> > select id, photo_name, length(photo) ln from temp_photo >> ID PHOTO_NAME LN >> ---------- -------------------------------------------------- ---------- >> 1 Blob length 4000 4000 >> 1 Blob length 8000 8000 >> >> >> I am not certain from which version LENGTH started supporting LOBS, so >> you don't have to use DBMS_LOB. >> The above was tested on 12.1.0.1.0, but I'm pretty certain it will work >> in 11 as well. >> >> >> >> Regards >> >> >> Kim Berg Hansen >> >> http://dspsd.blogspot.com >> kibeha@xxxxxxxxx >> @kibeha >> >> >> On Wed, Sep 17, 2014 at 5:10 PM, David Fitzjarrell <oratune@xxxxxxxxx> >> wrote: >> >> That won't work as you cannot call dbms_lob.getlength in a CHECK >> constraint: >> >> SQL> create table temp_photo >> 2 ( >> 3 ID NUMBER(3) NOT NULL, >> 4 PHOTO_NAME VARCHAR2(50), >> 5 PHOTO BLOB check(dbms_lob.getlength(photo) < 4097) >> 6 ); >> PHOTO BLOB check(dbms_lob.getlength(photo) < 4097) >> * >> ERROR at line 5: >> ORA-00904: "DBMS_LOB"."GETLENGTH": invalid identifier >> >> >> SQL> >> >> It was a good thought. >> >> David Fitzjarrell >> >> Principal author, "Oracle Exadata Survival Guide" >> >> >> On Wednesday, September 17, 2014 8:12 AM, Kim Berg Hansen < >> kibeha@xxxxxxxxx> wrote: >> >> >> Set a CHECK constraint on the length? >> >> >> >> Regards >> >> >> Kim Berg Hansen >> >> http://dspsd.blogspot.com >> kibeha@xxxxxxxxx >> @kibeha >> >> >> On Wed, Sep 17, 2014 at 4:01 PM, Jeffrey Beckstrom <JBECKSTROM@xxxxxxxxx> >> wrote: >> >> Is it possible to set a maximum size for a blob data type column? >> >> Jeffrey Beckstrom >> Lead Database Administrator >> Information Technology Department >> Greater Cleveland Regional Transit Authority >> 1240 W. 6th Street >> Cleveland, Ohio 44113 >> >> . >> >> >> >> >> >> >> >>