Large Objects as BLOB, CLOB In Oracle Database

In Oracle database users are allowed to manipulate and store binary objects like images or files. It is a very powerful side of Oracle that use SQL and PL/SQL language to play with binary data. In large systems only small data types such as VARCHAR2 are not satisfy requirements. Only 4000 bytes of characters can be stored in Oracle in CHAR types. If requirements are met, it can be possible to use extended data types as objects.

There is a small demostration that shows simply how to play with BLOBs:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> drop table docs;
Table dropped
SQL> create table docs( id number, content clob, add_date date);
Table created
SQL> INSERT INTO docs VALUES (1, empty_clob(), SYSDATE);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM DOCS;
ID         CONTENT                                 ADD_DATE
---------- --------------------------------------- -----------
1                                                  28.11.2006
SQL> DECLARE
  2    blob_pointer     CLOB;
  3    temp_string      VARCHAR2(1024);
  4    length_of_string NUMBER;
  5    length_of_blob   NUMBER;
  6  BEGIN
  7    SELECT content INTO blob_pointer FROM docs WHERE id = 1 FOR UPDATE;
  8    temp_string      := 'aassddffggh';
  9    length_of_string := length(temp_string);
 10    dbms_lob.OPEN(blob_pointer, dbms_lob.lob_readwrite);
 11    dbms_lob.WRITE(blob_pointer, length_of_string, 1, temp_string);
 12    length_of_blob := dbms_lob.getlength(blob_pointer);
 13 
 14    temp_string := NULL;
 15    dbms_lob.READ(blob_pointer, length_of_blob, 1, temp_string);
 16    dbms_output.put_line('Length of the BLOB is ' || length_of_blob);
 17    dbms_output.put_line('Content of BLOB is    ' || temp_string);
 18 
 19    temp_string      := 'qqwweerrtt';
 20    length_of_string := length(temp_string);
 21    dbms_lob.writeappend(blob_pointer, length_of_string, temp_string);
 22    length_of_blob := dbms_lob.getlength(blob_pointer);
 23 
 24    temp_string := NULL;
 25    dbms_lob.READ(blob_pointer, length_of_blob, 1, temp_string);
 26    dbms_output.put_line('Length of the BLOB is ' || length_of_blob);
 27    dbms_output.put_line('Content of BLOB is    ' || temp_string);
 28 
 29    dbms_lob.CLOSE(blob_pointer);
 30    COMMIT;
 31  END;
 32  /
Length of the BLOB is 11
Content of BLOB is    aassddffggh
Length of the BLOB is 21
Content of BLOB is    aassddffgghqqwweerrtt
PL/SQL procedure successfully completed
SQL> SELECT * FROM docs;
ID         CONTENT                                 ADD_DATE
---------- --------------------------------------- -----------
1          aassddffgghqqwweerrtt                   28.11.2006
SQL>