Q
Problem solve Get help with specific problems with your technologies, process and projects.

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;

Dig Deeper on Using Oracle PL-SQL

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close