CLOB in Oracle

Here is a table I will use for the rest of this entry:

CREATE TABLE large_objects (
char_data CLOB,
binary_data BLOB,
ext_binary_data BFILE
)

And now I'll insert a CLOB. If your data really was large, that's where DBMS_LOB would come into play. For this entry, I will stick to basics. I'll cover programming with LOBs in a future entry.

insert into large_objects (char_data)
values ('This is a lob even though it''s small');

And now we will select the data back and perform some string functions on it:

select char_data,
substr(char_data, 1, 7),
instr(char_data, '''')
from large_objects

char_data substr_char_data INSTR_CHAR_DATA
------------------------------ ------------------------------ ---------------
This is a lob even though it's This is 29
small

1 row selected.
To load files from the file system, look into DBMS_LOB.