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

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

Am Freitag, 26. Oktober 2012 09:30:12 UTC+2 schrieb Mladen Gogala:

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

Leo

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:4...

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

news:e5eb5615-b184-46f9-9292-ed851aa90d9e@googlegroups.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.
|

Am Freitag, 26. Oktober 2012 10:39:04 UTC+2 schrieb Jonathan Lewis:

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

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

news:3c3894bb-9a91-42a3-9e06-2b0fa0526298@googlegroups.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

Am Freitag, 26. Oktober 2012 12:10:53 UTC+2 schrieb Jonathan Lewis:

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

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?

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

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

Am Freitag, 26. Oktober 2012 23:37:44 UTC+2 schrieb Mladen Gogala:

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

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

That's OK.

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

--