How to set a maximum size for a BLOB field?

Navigation
1

I want to insert images and I want each record to have a maximum of 100M, if this value is exceeded, an error must be returned in the bank itself.

For illustration example of problem: (same as error is returned if we try to insert a string of 35 characters in a varchar (30) column, must do with the image bigger than 100M).

So if the uploaded image is larger than 100M the bank should report the error and reject the record, I will use the following table as an example and its generated error: 

CREATE TABLE imagem  
(  
  ID       NUMBER(5)  NOT NULL,  
  NOME     VARCHAR2(50),  
  IMG      BLOB(100M)  
)
  

Error at line 1 ORA-00907: missing right parenthesis

    
asked by anonymous 25.05.2016 / 13:24

1 answer

1

To solve your problem, we can put a constraint check , and enforce domain integrity by limiting the values that are accepted by a column.

They are similar to FOREIGN KEY as they control values that are placed in a column. The difference is how to determine which values are valid, constraint determine the valid values from a logical expression that is not based on data from another column.

In your case it can be solved with the expression: 

create table imagem
(
  id   number(5),
  nome varchar2(50),
  img  clob constraint CK_IMGMAX_100 check(length(img) < 100)
);

That is, we create a constraint for the image column called, CK_IMGMAX_100 and checking if the image size is less than 100, so the column only allows values less than 100 mb to be added.

Another interesting case of using, in a more usual example, is to check the size of the person's name, for example: 

ALTER TABLE imagem
ADD CONSTRAINT CK_IMAGEM
CHECK ( length(nome) BETWEEN 3 AND 50 );

So the nome field will only receive values between 5 and 50 characters.

    
25.05.2016 / 13:59
Rad Studio 10 Seattle - XMLTransformProvider - Encoding UTF-8 Static method with object as parameter