Problem solve
Get help with specific problems with your technologies, process and projects.
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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
