How to Insert Blob Datatype Values in Oracle 11g Database Through SQL*Plus

Question

I have created a table with Blob datatype but I do not know how to insert values into the table or view the table content using SQL*Plus. Please help me.

Solution

It depends on what kind of data you want put into a BLOB. Let's consider the table:

create table b1(id number , b blob);

If your data represented as hex-string you should use TO_BLOB function

insert into b1 values(1,to_blob('FF3311121212EE3a'));

SQLPLUS also shows BLOBs as hex-string

select * from b1;

----- -----------------------------------
   ID                                   B
----- -----------------------------------
    1 FF3311121212EE3A

Please refer Oracle documentation on Using LOBs

Comments

[user240677]

thanx Naeel for the reply. i am trying to store pdf file on my database. And i want to fetch the pdf file from the database to my jsp page. plz help me. its urgent!!!

[Naeel Maqsudov]

Look at this If file is stored on a server in the folder described with CREATE DIRECTORY then it is possible to use DBMS_LOB package to read the file and put it into a table. Regarding to JSP there are two ways, I think. 1) just extract to the folder accessable for the web-server, and give URL to the client. 2) load file and return it directly as http-response. See examlpe