Problem solve Get help with specific problems with your technologies, process and projects.

Inserting a blob field in SQL*Plus

It seems to be a very common question. I will be very happy if you could answer this again. When inserting a blob field in SQL*Plus, if I wish to insert an image "c:tempsample.jpg":

CREATE TABLE blob_table ( id NUMBER NOT NULL, images BLOB); INSERT INTO blob_table VALUES (15, ????);
Thanks Brian. Liam from Sydney

It is very difficult to use the SQL*Plus interface when working with LOBs. The SQL*Plus interface was never designed to handle LOBs and has not been updated to work with LOBs (at least not fully updated). And this makes sense to some degree. SQL*Plus is a text environment as opposed to a graphical environment. Most, but not all, LOBs are binary in nature. There are character LOBs (CLOB), but there are also binary LOBs (BLOB), and then there are binary files (BFILE). The binary large objects can be graphical images, Word documents, Excel spreadsheets, sound files, movies, or any other type of binary file. Since SQL*Plus is a text environment, how does one display a binary object in this environment? A major overhaul would have to take place in order for SQL*Plus to handle binary objects. This is probably the major reason why there is no native interface in SQL*Plus to handle LOBs.

The best that you can do in SQL*Plus is to use PL/SQL and the DBMS_LOB package. But your functionality with the DBMS_LOB package is pretty limited. The only type of LOB that you can load with the DBMS_LOB package is the BFILE datatype. You cannot use the DBMS_LOB package to load BLOBs. This means that in SQL*Plus, you cannot load BLOBs like you would like to. Instead, you'll have to use Java, OCI, Pro*C, Pro*Cobol, and Visual Basic (using Oracle Objects for OLE).

There is plenty more information in the Oracle8i Application Developer's Guide - Large Objects (LOBs).

For More Information

Dig Deeper on Oracle database design and architecture