Forum Stats

  • 3,836,885 Users
  • 2,262,201 Discussions
  • 7,900,133 Comments

Discussions

Load Multiple images (BLOBs) into the Database

61508381-1ef0-48eb-abb0-a47ebc86ab39
edited Apr 27, 2016 10:06AM in SQL Developer

I have a folder with several images. My database table is a simple table with 2 fields: name (char) and image (BLOB). How can I import my images located in my C disk (e.g.: C:\Users\me\images) into my table?

P.S. By using the SQL Developer I found a way to import 1 image per time, but I am looking for a solution that could import my entire folder; and when I am choosing import data from sql developer, I am getting an "There are no readers registered to the jpg type" error..

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Apr 27, 2016 2:03AM

    Hi 61508381-1ef0-48eb-abb0-a47ebc86ab39,

    One way would be to use Oracle sqlldr, available with Oracle client and server installs:

    Create a file with your images filenames that you want to load, for example:

    C:\> dir/b *.jpg *.png > files.txt
    C:\> type files.txt
    
    
    
    Oracle11g-Architecture.jpg
    sql.jpg
    ocr.jpg
    
    
    

    The directory listing search for jpg and png files found 3 jpg files.

    files.ctl control file referencing our list of image files in files.txt:

    LOAD DATA
    INFILE files.txt
    APPEND
    INTO TABLE images
    FIELDS TERMINATED BY ',' TRAILING NULLCOLS
    (
    name,
    image LOBFILE(name) TERMINATED BY eof
    )
    
    
    

    Load the data using sqlldr and our control file above:

    C:\> sqlldr user/[email protected] control=files.ctl
    
    
    

    The filenames and their respective image files data were loaded into the images table.

    SELECT name FROM images;
    NAME
    ------------------------------
    Oracle11g-Architecture.jpg
    sql.jpg
    ocr.jpg
    
    3 rows selected.
    
    
    

    Hope that helps,

    Gaz.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,682 Employee
    edited Apr 27, 2016 10:06AM

    @Kris Rice-Oracle has a blog post showing how to do this in SQLcl with javascript here:

       Kris' blog: SQLCL - Blob loading ALL the files

    Otherwise, SQL*Loader is probably the way to go. In fact, if you use SQLDev's Cart to unload a table with BLOBs,  you'll see the script we use to put the BLOBs back in is via SQL*Loader.

This discussion has been closed.