Re: Setting maximum size of a blob data type

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Thu, 18 Sep 2014 08:29:06 +0200
Message-ID: <CA+S=qd0fP2x=oRqgWQUyr9hs8ygM=0G5WEkw2Ah=mr_TgD7n6A_at_mail.gmail.com>



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 - 08:29:06 CEST

Original text of this message