Re: Setting maximum size of a blob data type
- From: Seth Miller <sethmiller.sm@xxxxxxxxx>
- To: dmarc-noreply@xxxxxxxxxxxxx
- Date: Wed, 17 Sep 2014 16:29:12 -0500
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
>
> .
>
>
>
>
>
>
>
>
Other related posts:
