Q

Query to find size of BLOB objects

I have a table named EMP with a BLOB data type for storing the photos of each employee. I want a query to find what the size is of that BLOB object.

In order to find out the size of LOBs in your database, you'll want to use the DBMS_LOB supplied PL/SQL package, specifically the GETLENGTH procedure. The Oracle docs give the following example on determine the LOB size:

 CREATE OR REPLACE PROCEDURE Example_11a IS lobd BLOB; length INTEGER; BEGIN -- get the LOB locator SELECT b_lob INTO lobd FROM lob_table WHERE key_value = 42; length := dbms_lob.getlength(lobd); IF length IS NULL THEN dbms_output.put_line('LOB is null.'); ELSE dbms_output.put_line('The length is ' || length); END IF; END;

Basically, you get the LOB locator from your table. This locator points to the LOB. Using this locator, you call the DBMS_LOB.GETLENGTH procedure to determine the LOB size.

 

For More Information


This was first published in October 2002

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