Toad World® Forums

How to store photo images in an Oracle database table?

Hello Y’all,

How do I store about 695 signs inventory photo images from a PDF file to an Oracle database table? We are using Oracle 10.2.0.2. What data type should I use for these images? How about the table space? What table space should I allocate to ensure there is no space issue?

Thanks in advance for any/all the help.

“linorchid” writes:

Hello Y’all,

Howdy,

How do I store about 695 signs inventory photo images from a PDF file to an
Oracle database table? We are using Oracle 10.2.0.2. What data type should
I use for these images? How about the table space? What table space should
I allocate to ensure there is no space issue?

You’re most likely looking for the BLOB datatype. As for the tablespace and
the amount of storage required, there’s not enough information here to help
you. The number of items to store is irrelevant. It’s like saying “I need
enough space to park 695 vehicles.” Are those vehicles SUVs or skateboards
or something in between or a mixture of all of the above? Without that
info, an accurate (or helpful!) answer can’t be given.

Your DBA will definitely need to help you. If you’re acting as the DBA, get
training – now! The Concepts manual is a great place to spend time until
then: http://www.oracle.com/pls/db102/homepage

Also, since this isn’t a question about Toad per se, you might have better
luck at the TOADSQL list: http://tech.groups.yahoo.com/group/TOADSQL/ or
perhaps the Oracle-L list: http://www.freelists.org/list/oracle-l

Good luck!

Rich – [TeamT]

Disclaimer: I also operate at 60 hurts.

Thanks for your response Rich. Please help me with the following:

Q: Can I use one of the Toad utilities(e.g. Import) to load the photo image and other data from a pdf file to an Oracle dB table? If not, any suggestions and/or thoughts on this?

We have about 695 signs photo images and an average of 1 and 1/2 MB per image. How do I calculate the table space for the image table?

Thanks again.

Toad can help you load the data. One at a time, by inserting a BLOG object to a
record field by pointing to the source file.

That will get tiring and you have 700 to load, you probably want to look at
SQLLDR – I THINK it supports BLOB/FILE uploads

The amount of space you need should be the same as the amount of disk those
images occupy now. They get copied up, bit by bit. So 695 X 1.5MB +/- 10%,
and then factor in growth…

You can read more about LOB storage in the oracle Docs

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#C
IHEBABG

I meant BLOB, not BLOG, but Toad can help you with that too J
http://www.youtube.com/watch?v=iahTXSXLgbM

the microsoft active-view opened a tiny window literally 50pixel high by 50
pixel wide window
that was neither interactive (you pressed escape to no avail) and did not allow
configuring…(operator cannot resize the window)
you may want to repackage your active view presentation as a shockwave file
this way all browsers with downloadable shockwave plugin will play your
presentation.

the youtube presentation was far easier to view and facilitative for creating
sql scripts…
the layout manager allows the operator to re-arrange execution order of
previously saved as well as newly created scripts

As some of us do not deploy to windows can the toad scheduler integrate to unix
cron?

Nice Job Jeff!
Martin


Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l’information seulement et n’aura pas n’importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Thanks for the helpful information Jeff.

You wrote:

Toad can help you load the data. One at a time, by inserting a BLOG object to a record field by pointing to the source file.

How? Please advise! Thanks.

You wrote:

you probably want to look at SQLLDR - I THINK it supports BLOB/FILE >uploads http://www.oracle-base.com/...LoadLobDataUsingSqlLoader.php

Yes the website is very helpful and it looks like I can try to us SQLLDR, but the example shown is using one LOB per file. My challenge would be: how to get each photo image from the PDF file to a sign file and use it as an input to the SQLLDR?

Any thoughts or suggestions on this?

Thanks again.

Sorry Jeff, I couldn’t view the youtub because our department has this web site blocked. Thanks anyway.

You double-click in the grid to edit the BLOB record, then use the ‘Open
File’ dialog to point to your image.

how to get each photo image from the PDF file to a sign file

Sounds like a good question for someone who’s an ADOBE/PDF expert or
Google.

Thanks Jeff. I tried your suggestion the way I understand it, but the stored images would not display. Please review the following and see if you could figure out what’s missing:

DROP TABLE test_lob_tab;

CREATE TABLE test_lob_tab (
photo_id NUMBER(16),
blob_content BLOB
);

INSERT INTO test_lob_tab VALUES (1, NULL);

Select * from test_lob_tab;

PHOTO_ID BLOB_CONTENT

To view it, you have to save it back to file, then open it.

Sounds like a good idea to just show the image natively though
image001.png

Sounds like a good idea to just show the image natively though

A totally other suggestion is to not store the file in the database, but only a
reference to the file and the location. BLOB’s are hard to handle in backups
and recoveries and also in scripts. And if you have to show the file using a
tool, it is already on the disk. And the application is a little bit different,
but not much.

In the company I work we generate a lot of PDF files that must be stored and
those are kept on seperate disks and when we are deciding to place them on
cheaper storage or new storage is it less a burdance on the system to do that.
Only copy the files, change the reference code, delete the file on the old
location and you are done. While moving BLOBs around is much harder.

It is a suggestion to handle things in different ways then storing it in the
database.

Groetjes,
Wim

On Sat, Mar 19, 2011 at 16:20, Jeff Smith wrote:

Sounds like a good idea to just show the image natively though

image001.png

A totally other suggestion is to not store the file in the database, but only a
reference to the file and the location. BLOB's are hard to handle in backups
and recoveries and also in scripts. And if you have to show the file using a
tool, it is already on the disk. And the application is a little bit different,
but not much.

In the company I work we generate a lot of PDF files that must be stored and
those are kept on seperate disks and when we are deciding to place them on
cheaper storage or new storage is it less a burdance on the system to do that.
Only copy the files, change the reference code, delete the file on the old
location and you are done. While moving BLOBs around is much harder.

It is a suggestion to handle things in different ways then storing it in the
database.

Groetjes,
Wim

On Sat, Mar 19, 2011 at 16:20, Jeff Smith wrote:

Sounds like a good idea to just show the image natively though

On Sun, 20 Mar 2011 08:45:41 +0100
Wim de Lange wrote:

A totally other suggestion is to not store the file in the database,
but only a reference to the file and the location. BLOB’s are hard to
handle in backups and recoveries and also in scripts. And if you have
to show the file using a tool, it is already on the disk. And the
application is a little bit different, but not much.

Which, for the Original Poster, is known as a BFILE column.

where MaxFileSize is 4GB
helpful informationis available at:
http://www.orafaq.com/wiki/BFILE

good call joseph!
Martin


Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l’information seulement et n’aura pas n’importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Afternoon,

How do I store about 695 signs inventory photo images from a PDF file to
an Oracle database table? We are using Oracle 10.2.0.2. What data type
should I use for these images? How about the table space? What table
space should I allocate to ensure there is no space issue?

Thanks in advance for any/all the help.

Given that you said that you were on 10.2 then here are a couple of
links showing exactly how to go about load images from files ON THE
DATABASE SERVER into a BLOB column in a table in a database.

http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_10.shtml

Now, as to how to size said tablespace that the table lives in, think of
a number, then double it!

Seriously, unless you are compressing (before loading?) then the table’s
image will be about the same size as the image on disc, plus a “bit” of
overhead.

Also, and I’m not 100% sure that this is (still) correct, editing a *LOB
column results in huge space overheads as the original is not edited in
place, but is copied elsewhere and a new *LOB row inserted in it’s place.

I would create table in normal tablespace, and make the BLOB column (and
index - there will be one!) live in a separate LOB tablespace set up
purely for that purpose.

Check out the LOB Storage clause on creating the table.

Good luck.


Cheers,
Norm. [TeamT]

Jeff,

In response to your statement of ‘To view it, you have to save it back to file, then open it.’, my questions are:

Q1: How to save the image of BLOB_CONTENT of test_lob_tab in the example from my last email back to a file?

Q2: What kind of file (e.g., .bmp, jpg, gif, pdf, etc) should I save it to?

The purpose of storing the photo images to an Oracle database table is to allow our super user to access them through either some kind of users interface or database inquery. That being said, My last, but not least, question is:

Q3: How to allow our users to access the image without my intervention for saving the stored image back to a file?

Thanks!

Thanks for all the information, resources and responses. I like all your comments. I found Wim’s suggestion of storing the reference only to the file is intriguing. It looks like there are a few methods that I could try out to store photo images in an Oracle database table. Now, a million dollars question is:

How do I allow my users to access these stored images without my intervention in the Oracle database?

p.s. Sorry, I should have clarified the purpose of storing the photo images in the Oracle database when I started my posting, and I hope it’s not too late…

Thanks again for all/any of the help.