Re: Setting maximum size of a blob data type
Date: Wed, 17 Sep 2014 09:53:38 -0700
Message-ID: <1410972818.29163.YahooMailNeo_at_web124706.mail.ne1.yahoo.com>
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,> 6 );
> 4 PHOTO_NAME VARCHAR2(50),
> 5 PHOTO BLOB check(dbms_lob.getlength(photo) < 4097)
> 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-lReceived on Wed Sep 17 2014 - 18:53:38 CEST