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 last published in January 2007

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close