Q

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


This was first published in June 2001

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close