Skip to Main Content
  • Questions

  • inserting data into a blob and retrieving it

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Shankar Ganesh.

Asked: July 19, 2001 - 7:39 pm UTC

Last updated: May 04, 2006 - 1:45 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom ,
I had written two procedures to write and read data from a blob.
they are as follows.

CREATE OR REPLACE PROCEDURE writeDataToLOB_proc IS
Lob_loc BLOB;
Buffer raw(32767);
Amount BINARY_INTEGER := 1;
Position INTEGER := 1;
i INTEGER;
len number;

BEGIN


buffer:='98765';

len:=length(buffer)/2;

insert into CREATE$JAVA$LOB$TABLE (name, lob, loadtime)
values ('1000', empty_blob(), sysdate);

SELECT lob INTO Lob_loc
FROM CREATE$JAVA$LOB$TABLE
WHERE name = '1000';


DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);

DBMS_LOB.WRITE (Lob_loc, len, 1, buffer );

DBMS_LOB.CLOSE (Lob_loc);

commit;
END;
/



CREATE OR REPLACE PROCEDURE ReadDataFromLOB_proc IS

Lob_loc BLOB;
Buffer raw(32767);
Amount BINARY_INTEGER := 1;
Position INTEGER := 1;
len number;


BEGIN

/* Select a LOB: */

SELECT lob INTO Lob_loc
FROM CREATE$JAVA$LOB$TABLE
WHERE name = '1000';


len:=DBMS_LOB.GETLENGTH(Lob_Loc);

dbms_output.put_line('THE LENGTH IS:'||len);

len := len*2;

DBMS_LOB.READ (Lob_loc,len, Position, Buffer);

dbms_output.put_line('the blob is read:'||Buffer);

END;
/

the problem is the display is not consistent.

if i write the value "1122"(even number of data)to the blob the display is coming correctly as "1122"

if i write the value "11223"(odd number of data) to the blob the display is coming as "011223"(a zero is getting appended in the beginning)

can u please tell me how to solve this issue..

thanks
Shankar Ganesh.

and Tom said...

STRONLY recommand you stop using CREATE$JAVA$LOB$TABLE. that is a system table and has a purpose.

Its the conversion between HEX and RAW thats getting you. You are using a RAW type but -- you are converting it to a character string (when you print it) and you are converting character strings to raws by assigning them.

So, for example:


buffer:='98765';

is really assigning:

buffer = 0x9 || 0x87 || 0x65;

3 raw characters were placed into the raw buffer.

You need to use the UTL_RAW package to deal with raws. Here are your routines (parameterized):



scott@ORA8I.WORLD> CREATE OR REPLACE PROCEDURE writeDataToLOB_proc( p_name in varchar2,
2 p_buffer in RAW )
3 IS
4 Lob_loc BLOB;
5 BEGIN
6 insert into t (name, lob, loadtime)
7 values (p_name, empty_blob(), sysdate)
8 RETURNING lob into lob_loc;
9
10 dbms_lob.writeAppend( lob_loc, utl_raw.length(p_buffer), p_buffer );
11 end;
12 /

Procedure created.

scott@ORA8I.WORLD>
scott@ORA8I.WORLD> CREATE OR REPLACE PROCEDURE ReadDataFromLOB_proc( p_name in varchar2 )
2 IS
3 Lob_loc BLOB;
4 BEGIN
5 SELECT lob INTO Lob_loc
6 FROM t
7 WHERE name = p_name;
8
9 dbms_output.put_line('THE LENGTH IS: '||dbms_lob.getlength(lob_loc));
10
11 -- just print out the first 200 bytes since put_line
12 -- cannot do more then 255
13 dbms_output.put_line('the blob is read: '||
14 utl_raw.cast_to_varchar2( dbms_lob.substr( lob_loc, 200, 1 ) )
15 );
16 END;
17 /

Procedure created.

scott@ORA8I.WORLD>
scott@ORA8I.WORLD> delete from t;

0 rows deleted.

scott@ORA8I.WORLD>
scott@ORA8I.WORLD> exec writeDataToLob_proc( '1001', utl_raw.cast_to_raw( '101023' ) );

PL/SQL procedure successfully completed.

scott@ORA8I.WORLD> exec writeDataToLob_proc( '1002', utl_raw.cast_to_raw( 'Hello World' ) );

PL/SQL procedure successfully completed.

scott@ORA8I.WORLD>
scott@ORA8I.WORLD> exec readDataFromLob_proc( '1001' );
THE LENGTH IS: 6
the blob is read: 101023

PL/SQL procedure successfully completed.

scott@ORA8I.WORLD> exec readDataFromLob_proc( '1002' );
THE LENGTH IS: 11
the blob is read: Hello World

PL/SQL procedure successfully completed.




Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

most useful solutions i had ever seen

shankar ganesh, July 20, 2001 - 12:15 pm UTC

i had always valued u r solutions much and it had given me a lot of lead into my project.
thanks a lot

Tell me how to ....

Son, April 01, 2002 - 12:00 am UTC

Can you tell me how to store picture in field Long Raw at my DB.
I uses Program VB to read picture and store it in my db.
Can you send for me some Document about VB and Oracle
thank you very much
I am looking forward to hearing from you.

Tom Kyte Rocks

Greg J, August 16, 2002 - 10:44 am UTC

I have been banging my head against this ASP problem where I could upload a file directly into the DB using a SQL insert and update but I couldn't get a stored procedure to do the same. I didn't realize my BLOB parameter should be a RAW for external datatypes. The method for testing in SQL*Plus was helpful too.

Display PDF stored in Database

Laila Mac, January 08, 2003 - 7:02 am UTC

Dear Sir
i have searched for a lot of oracle sites to find out the solution to display the pdf file inserted in the BLOB column of the table but failed to find out any solution.
I am using oracle 8i 8.1.6. Can you please tell me how can i display the pdf file stored in the database? I am using client/server
thanks

Tom Kyte
January 08, 2003 - 4:35 pm UTC

client server -- hmm, which if the 5,000 or so client server programming paradigms might you be using.

Then, maybe I can point you to what you need.

(people still do client server??)


Horrible Response!!!!!

Laila Mac, January 08, 2003 - 11:24 pm UTC

Sorry! but this is a horrible response.
everybody cannot move to web. What about those who are using oracle/developer 6i in client/server. should they move to some other database environment to get desired functionalities???
If you cannot easily retrieve files inserted in the blob column then what is the use of storing such files in the database. If we have to use Java, pro C or other 3gl for getting full advantage from oracle then it means oracle has nothing without the existence of java etc.

Tom Kyte
January 09, 2003 - 7:05 am UTC

dude -- settle yourself back down into your chair and tell me

WHICH OF THE 5,000 CLIENT SERVER OPTIONS ARE YOU USING AND I'LL TELL YOU WHAT TO DO SINCE YOU DON'T SEEM TO BE ABLE TO FIND IT IN THE DOCUMENTATION YOURSELF.

eh? cannot you answer the question? tell me the tool you are using and I'll point you to information. I'm not going to do it for the 5,000 tools that are out there.

What a horrible comment from you.

My goodness

Alexandre, January 09, 2003 - 10:07 am UTC

Tom,

Our dear pope should canonize you...
Too much patience with such people above...

[]



Tom Kyte
January 09, 2003 - 1:27 pm UTC

you mean you couldn't tell it was getting pretty stretched on that follow of mine?

communication problem

hrishy, January 10, 2003 - 12:22 am UTC

Hi Tom

Laila Mac is using Developer6i..so she is looking for a solution with forms and reports..You could point her to that solution tom..

Developer 6i

Laila Mac, January 11, 2003 - 1:00 am UTC

I am using Developer6i with oracle 8.1.6

Tom Kyte
January 11, 2003 - 8:20 am UTC

You would use the OLE object embedding in all probability.

There should be an activex control capable of showing a pdf -the rest just "sort of happens". Not being a windows programmer, thats as far as I can go into the tool itself -- suggest you goto otn.oracle.com under discussion forums - there you will find a forms discussion group. Odds are, someone there can be more specific.

There are even support notes on metalink.oracle.com that discuss this in detail. <Note:76602.1> for example is

How to Integrate Adobe Acrobat Reader into Oracle Forms Via An OCX Control

Unable to see thePDF

praveen, March 07, 2003 - 7:30 am UTC

Hi Tom
I have sucessfully executed this code for Package.
When I am executing this package I am getting
Procedure succesfully completed,but unable to see the PDF.
can u guide me hoe can i see the pdf
from pl/sql

Tom Kyte
March 07, 2003 - 8:12 am UTC

you will never see the pdf in sqlplus -- it is just "stuff"

you can use owa_util.showpage to dump it, but it'll just be gibberish on the screen.

What if greater then 32k?

Sasa, August 20, 2003 - 10:34 am UTC

Hi Tom,

What if in your example in procedure writeDataToLOB
param p_buffer is greater then 32k?
We will get an error ORA-06502: PL/SQL: numeric or value error.
How to get proper chunks in procedures?
Should we substr raw(to limit to 32k)and do writeappend with this chunk?
In that case we would have loop with count maximum number of chunks?
For example if image has 250k we would have something like:
for 1..ceil(250000/32000)
loop
substr p_buffer to 32k
writeappend(with previosly created chunk);
end loop;

Am I correct or I am in the wrong hole ?

Tom Kyte
August 21, 2003 - 8:20 am UTC

you would just call dbms_lob.writeAppend over and over in another procedure if you like. Once the blob is there, you can select it out, writeAppend it.


you cannot have a p_buffer in plsql > 32k so the psuedo code you have won't fly.


the CLIENT would send 32k at a time.

archive BLOB data offline

Raghu, September 02, 2003 - 8:35 am UTC

our application requires a functionality which
will archive application files offline.

the files used by the application is stored in the
database as BLOB table.

the user will select the files which he wishes to archive and these
files should be removed from the database and stored offline outside database in a file system.

later when the user requires them these files should be
retrieved back.

any suggestion as how this should be implemented.

Background of the functionality
------------------------------
many departments are using the application.Each department pays for the
space they use in the database. when the sizes of all the files stored
by the department exceeds the space allocated to them, then the department
has to remove some files from the database.

but these files should be stored somewhere so that these can be brought back
into the database.

the relation data other then files are never deleted.they always exists in the
database.

So can you suggest with your experience as how can i implement this functionality.

Tom Kyte
September 02, 2003 - 8:52 am UTC

I would strongly encourage you to rethink the priorities here.

one you remove them from the database the requirement:

"but these files should be stored somewhere so that these can be brought back
into the database."

is virtually UNOBTAINABLE. Not only that but what have you done for yourself? why does it cost you more to leave the data be in the database where it is protected, secured, backed up (and more importantly recoverable), professionally managed?

it seems to me it would be infinitely more costly to store the data OUTSIDE of the database. You'll be chasing down files, you'll be backing up bits and pieces all over the place, you'll hit file system limits and have to come up with fancy algorithms to splay the data over many mount points, and you'll have to come up with a way to remember where the files are (and heaven forbid someone MOVES them)

My approach would be to charge departments more for the less space they use. To encourage the centralization of document storage. To protect my companies assets. In Oracle, we have a single multi-terabyte file store in the database. No quotas -- no chargebacks -- just a plea from IT to put everything you got in there so we don't lose it.

Data is money, data is your company. Disk is cheap compared to losing stuff.


in order to take the blob out and put it on disk you would need to code either a "client" that runs on the user desktop if you want the files on their machines (really bad idea) or a java stored procedure/C based external procedure on the server to write to the servers file system (really bad idea #2)



A reader, September 02, 2003 - 9:15 am UTC

Thanks for the prompt reply.

But data into our application comes automatically from test benches or measurement devices like oscilloscope.

for eg. an experiment performed with an oscilloscope results in huge amounts of data within no time.

So the idea from the customer was if an offline archiving functionality can be provided then these can be archived offline from the database and bring them only when required.

would you still stand by storing these results into the database.

Tom Kyte
September 02, 2003 - 10:39 am UTC

is this "archived" data of any value whatsoever to you.

Raghu, September 02, 2003 - 11:06 am UTC

Yes, basically these are results of the experiment. so the user might require this .

Currently we are in the requirements phase and the user has given such a requirement. so i thought of having your opinion.

if you can suggest some alternative solution.

Tom Kyte
September 02, 2003 - 2:54 pm UTC

well, you have my opinion -- data of value is data you keep in a database.

anything not of value, you can do with what you like.

So, my solution would be to get enough disk to hold my data..

you really haven't talked volumes here, that'll be something you want to consider. if you are talking about petabytes of raw number files -- maybe you want a really good HFS (hierarchical file system) and spend the time making sure you can reliably link pointers (bfiles) in Oracle to this specialized storage system to retrieve this data. It'll be code, it'll be rules, it'll be harder -- but you might find the cost of a big old HFS with different kinds of storage to be of use.


If you are talking giga/tera bytes -- i would be looking at a database only solution.

Excellent

Raghu, September 02, 2003 - 11:39 pm UTC

Thanks a lot Tom.I have your opinion in mind.
I will discuss the same with the customer.

Another question.
I already have your expert one-on-one from WROX.
How different are the two new books(Effective Oracle By Design and Expert One-On-One) compared to the one i already have.

your book is very useful in day today life and i have learnt a lot from it.


Tom Kyte
September 03, 2003 - 6:56 am UTC

they are night and day different.

you can read the sample chapters online for the new book -- link on the home page. decide for yourself.

Have u visited India

A reader, September 05, 2003 - 3:02 am UTC

Have u ever visited bangalore.
Is ther any plans in future.

pdf greater than 32k - reading and storing from java using Stored procedure

KJ, May 03, 2006 - 4:44 pm UTC

Hi Tom,
we could use dbms_lob.writeappend and inside a loop from java call a stored procedure that uses writeappend to insert blob data. What if the file size is 300k or more? From our Middle Tier team they raised a concern looping to send that data. Can they send the data in One Chunk? How do we do it? Can you help me here?
Thanks
KJ

Tom Kyte
May 04, 2006 - 1:45 am UTC

Middle tier team should read:

</code> http://docs.oracle.com/docs/cd/B19306_01/java.102/b14355/toc.htm 

specifically
http://docs.oracle.com/docs/cd/B19306_01/java.102/b14355/jstreams.htm#sthref849 <code>

on how to stream lob data with java.

Thanks a ton Tom

KJ, May 04, 2006 - 4:50 pm UTC

As usual...
You are simply great Tom. wish i'd get a chance to work with you someday!!!

Regards


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here