Home -> Mailing Lists -> Oracle-L -> Re: Setting maximum size of a blob data type

Re: Setting maximum size of a blob data type

From: Seth Miller <sethmiller.sm_at_gmail.com>
Date: Thu, 18 Sep 2014 11:32:06 -0500
Message-ID: <CAEueRAWHVUiU_oZxVaqZsnA7cpe_oyhqa-S1MW1aLsH0Z4Ogkw_at_mail.gmail.com> 





Kim,



I was looking at the wrong documentation.



Seth Miller



On Thu, Sep 18, 2014 at 1:29 AM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:



> 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_at_gmail.com

> _at_kibeha

>

>

> On Wed, Sep 17, 2014 at 11:29 PM, Seth Miller <sethmiller.sm_at_gmail.com>

> 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_at_freelists.org> 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_at_gmail.com> 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_at_gmail.com

>>> _at_kibeha

>>>

>>>

>>> On Wed, Sep 17, 2014 at 5:10 PM, David Fitzjarrell <oratune_at_yahoo.com>

>>> 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_at_gmail.com> wrote:

>>>

>>>

>>> Set a CHECK constraint on the length?

>>>

>>>

>>>

>>> Regards

>>>

>>>

>>> Kim Berg Hansen

>>>

>>> http://dspsd.blogspot.com

>>> kibeha_at_gmail.com

>>> _at_kibeha

>>>

>>>

>>> On Wed, Sep 17, 2014 at 4:01 PM, Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org

>>> > 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

>>>

>>> .

>>>

>>>

>>>

>>>

>>>

>>>

>>>

>>>

>



--
http://www.freelists.org/webpage/oracle-l


Received on Thu Sep 18 2014 - 18:32:06 CEST












Original text of this message