1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

IS it possible to store images, audios and videos in table in DB?!

Discussion in 'SQL PL/SQL' started by Vicky, Aug 20, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    IS it possible to store images, audios and videos in table in DB?! If, yes could U tell me how to store and retrieve it successfully.,.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, you can do it directly or indirectly. For direct storage you'll need to create a BLOB column and use the dbms_lob package to get the data into that field. For indirect storage you would use a BFILE and use the dbms_lob package to generate a lob locator to store in the BFILE column. The locator is associated with the external location of the image. Here is an example:


    Code (SQL):

    SQL> CREATE TABLE temp_photo
      2   (
      3   ID    NUMBER(3)     NOT NULL,
      4   PHOTO_NAME         VARCHAR2(50),
      5   PHOTO       BLOB
      6   );


    TABLE created.


    SQL>
    SQL> CREATE OR REPLACE directory photo_dir AS 'C:\yarp';


    Directory created.


    SQL>
    SQL> DECLARE
      2    v_lob     BLOB;
      3    v_id      NUMBER;
      4    v_name    varchar2(50);
      5    l_bfile   bfile;
      6    amt       NUMBER;
      7  BEGIN
      8    l_bfile := bfilename( 'PHOTO_DIR', 'df.jpg' );
      9
     10    INSERT INTO temp_photo (id, photo_name, photo)
     11    VALUES ( 1, 'My Photo',empty_blob() )
     12    returning id, photo_name, photo INTO v_id, v_name, v_lob;
     13
     14    amt := dbms_lob.getlength( l_bfile );
     15    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
     16    dbms_lob.loadfromfile( v_lob, l_bfile ,amt);
     17
     18    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
     19    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
     20    dbms_lob.fileclose( l_bfile );
     21  END;
     22  /
    File LENGTH IS: 166910
    Loaded LENGTH IS: 166910


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM temp_photo;


            ID PHOTO_NAME
    ---------- --------------------------------------------------
    PHOTO
    --------------------------------------------------------------------------------
             1 My Photo
    FFD8FFE000104A46494600010101012C012C0000FFE0C6CA4A46585800136AA01D1D1F1C1D1F1D1D
    1F1D1E1F1D1E201E1E201F1F211E1F211E1F211E1F211E1F211F1F211E1F211F1F212020221F2022

    SQL>
    SQL> DECLARE
      2    v_lob     BLOB;
      3    l_bfile   bfile;
      4    amt       NUMBER;
      5  BEGIN
      6    l_bfile := bfilename( 'PHOTO_DIR', 'df_old.jpg' );
      7
      8    SELECT photo INTO v_lob FROM temp_photo WHERE id=1;
      9
     10    amt := dbms_lob.getlength( l_bfile );
     11    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
     12    dbms_lob.loadfromfile( v_lob, l_bfile ,amt);
     13
     14    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
     15    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
     16    dbms_lob.fileclose( l_bfile );
     17  END;
     18  /
    File LENGTH IS: 166910
    Loaded LENGTH IS: 166910


    PL/SQL PROCEDURE successfully completed.


    SQL>