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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.
This was first published in June 2001