Ask the Expert

Finding size of files in BLOB datatype

I have a table named DOCUMENT with a BLOB datatype for storing files (DIC, PDF, XLS, etc.). I want a query to find what the size is of each file stored in that BLOB object.

    Requires Free Membership to View

I have a table called DEMO. It has a field called THEBLOB which has a datatype of BLOB. Using the package DBMS_LOB and its procedure GETLENGTH, I can find the length of the blob. See my example below. Also, note that I have several types of files inserted: PDF, MSWORD, BMP and JPEG.
SQL> desc scott.demo
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 THEBLOB                                            BLOB
 FILE_NAME                                          VARCHAR2(50)
 CENTER_ID                                          VARCHAR2(5)
 MIMI_TYPE                                          VARCHAR2(20)
 THELONG                                            LONG RAW

SQL> l
  1  select substr(file_name,1,20) Name, MIMI_TYPE, dbms_lob.GETLENGTH(theblob)
  2* from scott.demo
SQL> /

NAME                 MIMI_TYPE            DBMS_LOB.GETLENGTH(THEBLOB)           
-------------------- -------------------- ---------------------------           
Design and Construct application/pdf                           682183           
Lease Agreement - 18 application/pdf                          2301417           
Agreement in Princip application/pdf                           353304           
User Manual          application/MSWORD                       1089536           
                     unknown                                                    
Job Corps.bmp        image/bmp                                  64062           
01-New VST Retail Sa image/jpeg                                 89525           
Agreement in Princip application/pdf                           353304           
Design and Construct application/pdf                           682183           
Lease Agreement - 18 application/pdf                          2301417           

10 rows selected.

SQL> spool off;

This was first published in January 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: