Selecting a BLOB Column and why TOAD displays ""(HUGEBLOB)" or "(HugeBlob)"

Toad for Oracle
1

I am select a BLOB column from an Oracle table and TOAD is displaying "(HUGEBLOB)" or "(HugeBlob)". Normally I have always understood this to indicate if the column is NULL or not. If I see "(HugeBlob)" it is NULL and if I see "(HUGEBLOB)" it is not NULL. However, I am seeing "(HUGEBLOB)" and the column is NULL. This is the SQL I am running:

select * from sys.scheduler$_job_output

2

Welcome to the Toad/Oracle Forum, Chris.

I never noticed that disctinction, but now am seeing this as well (the nuance of lower/upper case denoting whether the value is null or not.

However, not able to reproduce your observed issue (not using the same table as you listed, incidentally)... is it possible that the data grid has not been visually refreshed?

3

Hey Chris,

You are right, Uppercase means not null and Mixed means null.

If I remember correctly, the EMPTY_BLOB function, as below, will make it not null, but empty, and uppercase. That's probably what is happening.

UPDATE table SET column = EMPTY_BLOB() where...

4

Thanks for the comments. I think this is my first time on these forums although I have used TOAD for about 20 years.

I am asking because I have a Ticket opened with oracle about DBMS_SCHEDULE not posting to ALL_SCHEDULER_JOB_RUN_DETAILS.OUTPUT. This is a view on the SCHEDULER$_JOB_OUTPUT table. When I select * from SCHEDULER$_JOB_OUTPUT the table with TOAD. I see the "(HUGEBLOB)" that indicates to me that DBMS_SCHEDULE wrote a NULL value into the column rather than just not touching it at all. I was trying to see if antone might have a thought on that as the distinction that TOAD is making?

5

It is as I said in my reply.

You are correct in assuming that (HugeClob) = null and (HUGECLOB) = not null.

However, an empty blob is not the same as a null blob.

Try this and see for yourself:

create table blob_null_test
(col1 varchar2(30), col2 blob);

insert into blob_null_test
(col1, col2)
values
('null', null);

insert into blob_null_test
(col1, col2)
values
('empty_blob', empty_blob());

insert into blob_null_test
(col1, col2)
values
('not_null', 'abc');

commit;

select * from blob_null_test;

Oh, and by the way, I'm really happy to see a "new" 20 year user here. :smiley:

6

Perfect. Yes, my exact thoughts. Now I just need to work out why. Thanks for the help!!!

1 Like
7

I've always disliked the (HugeClob) and (HUGECLOB) display, it's not at all intuitive from a user's point of view.

I get the ternary situations : empty_clob(), null clob, clob with content, and get you don't want to fetch the lob content to keep grids performant, but why not make it clearer or at least make it user configurable?

This, in my opinion, is better (or something similar)...
(Null Clob)
(Empty Clob)
(Clob...) or (Clob Content...)

8

Paul, I agree completely. That text comes from Delphi's source. I may be able to change it though. Great suggestion.

1 Like