Re: Setting maximum size of a blob data type

  • From: "David Fitzjarrell" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "oratune@xxxxxxxxx" for DMARC)
  • To: Kim Berg Hansen <kibeha@xxxxxxxxx>
  • Date: Wed, 17 Sep 2014 09:53:38 -0700

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

Other related posts: