Q

Finding size of files in BLOB datatype

I have a table named DOCUMENT with a BLOB datatype for storing files. I want a query to find the size of each file stored in that BLOB object.

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.
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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close