Inserting BLOBs from files with the filename stored in another column

[leicht...@gmail.com]

Hello all,

we need to migrate article images into an existing table; the name of each article's image file is stored in a column of table Articles, basically:

ARTICLES
--------
IMG_FILENAME [varchar2(100)] | IMAGE [blob]

I found nothing helpful so far on the web. Is there a simple way to do this in PL/SQL or do I have to use an external script?
Any help would be greatly appreciated.

Regards, Leo

[Mladen Gogala]

On Thu, 25 Oct 2012 23:18:14 -0700, leichtenfels wrote:

> I found nothing helpful so far on the web. Is there a simple way to do
> this in PL/SQL or do I have to use an external script?
> Any help would be greatly appreciated.
>
> Regards, Leo

Perl, Python or even PHP are all able to do it. A simple script which
loads BLOB into a database can be found here:

http://www.dba-oracle.com/t_dbi_interface1.htm

This is written in Perl. PHP version is written in Pro PHP Programming, by
Apress books: http://tinyurl.com/cknhn3j

This can also be written in Python, but that is not my forte.





--
Mladen Gogala
http://mgogala.freehostia.com

[leicht...@gmail.com]

Am Freitag, 26. Oktober 2012 09:30:12 UTC+2 schrieb Mladen Gogala:
>
> Perl, Python or even PHP are all able to do it.

Thanks. I would actually prefer PL/SQL because we want to integrate it into our regular migration process.

Leo

[Jonathan Lewis]

I assume the problem is finding code that allows you to read the list of
files from the directory.

To do that from pl/sql I think you still need to write a little Java in the
database - someone who is more familiar with the latest features of pl/sql
may point out that I'm wrong, of course. Here's a link to a very old
example of the method from Tom Kyte.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584

The thread started in 2000, so better read browse through the more recent
years as well, rather than stopping at the first answer, in case there are
updates and improvements mentioned.

Once you've got the list of files I assume you will have no problems with
the relevant calls to the dbms_lob package.

--

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

<leicht...@gmail.com> wrote in message
news:e5eb5615-b184-46f9...@googlegroups.com...

[leicht...@gmail.com]

Am Freitag, 26. Oktober 2012 10:39:04 UTC+2 schrieb Jonathan Lewis:
> I assume the problem is finding code that allows you to read the list of
> files from the directory.

Actually my problem is iterating through the table and loading the file into the BLOB column. As the file name is stored in a column I don't need to process the list of files (though this would be certainly doable).

This may be a basic question, but I'm looking for something along the lines of

UPDATE ARTICLES SET IMAGE = LOAD_IMAGE_FILE(IMG_FILENAME);

where LOAD_IMAGE_FILE is a stored procedure that simply returns the file contents as a BLOB.

Would this work? What should the stored procedure look like? I'm sorry if this question seems trivial but I couldn't find anything helpful on the web.

Regards, Leo

[Jonathan Lewis]

Here's an example of the type of code needed to get a BLOB returned by a
function call, but I'm not sure that you could use the return value in a
simple update statement - mainly because this isn't the way I've loaded
BLOBs into tables in the past.
http://technologydribble.info/tag/load-blob-from-file/

The code I've used in the past is similar to the above, but is based on
single row processing, starting with an empty_blob() in the blob column.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

<leicht...@gmail.com> wrote in message

news:3c3894bb-9a91-42a3...@googlegroups.com...

[leicht...@gmail.com]

Am Freitag, 26. Oktober 2012 12:10:53 UTC+2 schrieb Jonathan Lewis:
> Here's an example of the type of code needed to get a BLOB returned by a
>
> function call, but I'm not sure that you could use the return value in a
>

Hi, thanks for the reply; I think I figured it out now. I first declare a procedure:

CREATE OR REPLACE DIRECTORY
BLOB_DIR
AS
'c:\temp';

CREATE OR REPLACE PROCEDURE BLOB_LOAD
(filename IN varchar2, filecontent OUT NOCOPY blob)
AS
tmpBlob blob;
lFile BFILE := BFILENAME('BLOB_DIR', filename);
BEGIN
dbms_lob.createtemporary(tmpblob, TRUE);

DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

DBMS_LOB.OPEN(tmpBlob, DBMS_LOB.LOB_READWRITE);

DBMS_LOB.LOADFROMFILE(DEST_LOB => tmpBlob,
SRC_LOB => lFile,
AMOUNT => DBMS_LOB.GETLENGTH(lFile));

DBMS_LOB.CLOSE(lFile);
DBMS_LOB.CLOSE(tmpBlob);

filecontent := tmpBlob;
END;


Then I call it using a cursor:

declare
cursor tblCursor is
select * from articles;
begin
for tblRec in tblCursor loop
BLOB_LOAD(tblRec.img_filename, tblRec.image);
update articles set image = tblRec.image where img_filename = tblRec.img_filename;
end loop;
commit;
end;

I don't know, if this is good style, but it seems to work.
Sorry for the trouble, regards,
Leo

[Mladen Gogala]

On Fri, 26 Oct 2012 03:32:14 -0700, leichtenfels wrote:

>
> I don't know, if this is good style, but it seems to work.
> Sorry for the trouble, regards,
> Leo

I am currently on the client site and cannot compare the speed. I will try
pitting your script against a Perl script the next weekend. My bet is on
the Perl script.
I will use RDBMS 11.2.0.3.4, the latest and the greatest. For a comparison
to make sense, I have to ask what version of the database are you using
and how is the LOB segment created? I am interested in CACHE/NOCACHE
attributes. Also, are you using flashback DB?

[Gerard H. Pille]

He might very well beat the php if he added a freetemporary, otherwise his routine might slow
down and run out of memory.

[leicht...@gmail.com]

Am Freitag, 26. Oktober 2012 23:37:44 UTC+2 schrieb Mladen Gogala:
> I am currently on the client site and cannot compare the speed. I will try
>
> pitting your script against a Perl script the next weekend. My bet is on
>
> the Perl script.
>
> I will use RDBMS 11.2.0.3.4, the latest and the greatest. For a comparison
>
> to make sense, I have to ask what version of the database are you using
>
> and how is the LOB segment created? I am interested in CACHE/NOCACHE
>
> attributes. Also, are you using flashback DB?

Sorry, I'm a software developer, not an Oracle DBA, and I know almost nothing about the things you ask. Also, performance is not my main concern; this thing needs to run only once so I don't optimize here. However, if you are interested in a performance comparison I can send you the dbca template file that I used to create the database.

Regards, Leo

[Mladen Gogala]

On Mon, 29 Oct 2012 00:22:41 -0700, leichtenfels wrote:

> Sorry, I'm a software developer, not an Oracle DBA,

To quote the famous movie with Marilyn Monroe, nobody is perfect.
http://www.youtube.com/watch?v=2Inp_sWsUqQ

> and I know almost
> nothing about the things you ask. Also, performance is not my main
> concern; this thing needs to run only once so I don't optimize here.

That's OK.

> However, if you are interested in a performance comparison I can send
> you the dbca template file that I used to create the database.
>
> Regards, Leo

No, not necessary. I will make my comparison and report the results.


--