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!

How to check blob data (image) size

Discussion in 'SQL PL/SQL' started by soushi01, Nov 20, 2013.

  1. soushi01

    soushi01 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I need to query image size (mb)
    Here is table and datatype , for BOOK_PHOTO is store (BLOB) file

    CREATE TABLE BOOK_MASTER
    (
    BOOK_NAME VARCHAR2(30 BYTE) NOT NULL,
    BOOK_PHOTO LONG RAW
    );


    So i need to query image/photo size (prefer .mb)

    when i query like this
    select dbms_lob.getlength(BOOK_PHOTO) from BOOK_MASTER

    is show error " ORA-00997: illegal use of LONG datatype " :(

    any solution ?

    thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, use a BLOB data type rather than LONG RAW. Your table definition doesn't support using the dbms_lob procedures and functions. LONG RAW and BLOB are not the same:

    Code (SQL):
    SQL> --
    SQL> -- Create table
    SQL> --
    SQL> -- Use LONG rather than BLOB
    SQL> --
    SQL>
    SQL> CREATE TABLE BOOK_MASTER
      2  (
      3  BOOK_NAME VARCHAR2(30 BYTE) NOT NULL,
      4  BOOK_PHOTO LONG RAW
      5  );
     
    TABLE created.
     
    SQL>
    SQL> --
    SQL> -- Try to find book_photo size
    SQL> --
    SQL> -- BOOK_PHOTO is not a BLOB so this won't work
    SQL> --
    SQL>
    SQL> SELECT dbms_lob.getlength(BOOK_PHOTO) FROM BOOK_MASTER
      2  /
    SELECT dbms_lob.getlength(BOOK_PHOTO) FROM BOOK_MASTER
                              *
    ERROR at line 1:
    ORA-00997: illegal USE OF LONG datatype

    SQL>
    SQL> SELECT LENGTH(book_photo) FROM book_master;
    SELECT LENGTH(book_photo) FROM book_master
                  *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY

    SQL>
    SQL> --
    SQL> -- Redefine the table
    SQL> -- and try again
    SQL> --
    SQL>
    SQL> DROP TABLE BOOK_MASTER purge;
     
    TABLE dropped.
     
    SQL>
    SQL> CREATE TABLE BOOK_MASTER
      2  (
      3  BOOK_NAME VARCHAR2(30) NOT NULL,
      4  BOOK_PHOTO BLOB
      5  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT INTO book_master(book_name, book_photo) VALUES('Smezzo Flormnimple', empty_blob());
     
    1 ROW created.
     
    SQL>
    SQL> --
    SQL> -- Now try using dbms_lob
    SQL> --
    SQL>
    SQL> SELECT dbms_lob.getlength(BOOK_PHOTO) FROM BOOK_MASTER
      2  /
     
    DBMS_LOB.GETLENGTH(BOOK_PHOTO)
    ------------------------------
                                 0
     
    SQL>
     
    soushi01 likes this.