7 Replies Latest reply: Mar 21, 2011 11:55 PM by Billy~Verreynne RSS

    How to store photo images in a database table?

    Orchid
      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.
        • 1. Re: How to store photo images in a database table?
          768636
          This would help -

          http://www.dba-oracle.com/t_storing_insert_photo_pictures_tables.htm
          • 2. Re: How to store photo images in a database table?
            Nikhil Juneja
            BLOB is one of the option.

            try google or asktom for it..

            need to put ur images in an oracle directory.. and then upload the file using bfile

            in case still unable to find the exact code do reply back..i will post it here

            i recently worked on it..

            ~Nik
            • 3. Re: How to store photo images in a database table?
              Orchid
              Thanks for your response Nik. Could you please post your code here?

              Thanks again.
              • 4. Re: How to store photo images in a database table?
                Nikhil Juneja
                Here you go..

                Assuming you have a table
                test_blob (col1 BLOB);

                Create an oracle directory or use an existing one to place the image on the oracle server.
                create or replace directory tmp_blob as '/tmp';
                This tmp location should exist on your server where oracle is installed.

                also ftp the image file (say image1.bmp) to this location
                declare
                l_blob BLOB;
                l_bfle BFILE;
                l_re BOOLEAN := FALSE;
                Begin
                DBMS_LOB.createtemporary(l_blob, TRUE);
                
                insert into test_blob(col1) values (EMPTY_BLOB())
                returning col1 into l_blob;
                
                --return image into l_blob;
                l_bfile := BFILENAME('TMP_BLOB','image1.bmp');
                
                --check if file exists
                
                l_ret := DBMS_LOB.fileexists (l_bfile) = 1;
                
                if (l_ret)
                then
                DBMS_LOB.fileopen(l_bfile);
                
                DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
                commit;
                else
                DBMS_OUTPUT.put_line('File not present');
                end if;
                end;
                /
                not tested this exact one.. check for the syntax errors if any.. this should work
                • 5. Re: How to store photo images in a database table?
                  Orchid
                  Great and thanks Nik for the code. Assuming the images are stored in an Oracle database table, how do you retrieve and view the images from the table?

                  Thanks again for any/all the help.
                  • 6. Re: How to store photo images in a database table?
                    Nikhil Juneja
                    Hi..

                    We use oracle reports to fetch the image on a PDF..

                    In case you want to view only, then after you select the query using any tool (say TOAD or SQl Developer), you can save the image to your disk and see, if it was uploaded in the database table successfully.

                    ~Nik
                    • 7. Re: How to store photo images in a database table?
                      Billy~Verreynne
                      Orchid wrote:

                      Assuming the images are stored in an Oracle database table, how do you retrieve and view the images from the table?
                      That depends entirely on the client.

                      If it is a SQL client, it needs to select the BLOB from the table and render it as an image. Certain clients, like SQL*Plus, cannot do this - in which case you need to use the client to save the BLOB as a local file and then use a viewer to view it.

                      Remember to store the Mime type with the BLOB in the table. This is important for delivering web content and interfacing with clients that need to render the contents.

                      In the case of a web client, you can use WPG_DOCLOAD.download_file() to stream the image to a web browser. I posted example code in {message:id=1571390} that demonstrates the basics of using this PL/SQL interface.