Thanks for the question, Steve.

Asked: February 19, 2007 - 12:26 pm UTC

Last updated: February 21, 2007 - 11:08 am UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

What's the different between null and empty_clob()? And, is a null represented different for a clob then a varchar?

create table tt1(n1 number, c1 varchar2(1), b1 clob)

insert into tt1 values(1, null, null)

insert into tt1 values(2, null, empty_clob())

select * from tt1

If I do this in TOAD, for example, it knows c1 is null and shows "{null}", but for b1 it does not. (I know TOAD may do things different then SQLplus, but it raised the question in my mind.)

Thanks,
Steve

and Tom said...

a NULL is the absence of a value - nothing

an empty_clob() is a value - it is not null, it is just "empty", no data in it yet.

The empty_clob() is not NULL, it is an empty clob of length zero.

sort of like this:

[tkyte@tkyte-pc ~]$ ls empty_clob
ls: empty_clob: No such file or directory
[tkyte@tkyte-pc ~]$ touch empty_clob
[tkyte@tkyte-pc ~]$ ls empty_clob
empty_clob
[tkyte@tkyte-pc ~]$

the first ls - that is a null clob

the second ls - that is an empty_clob - zero length file.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Representation for client

Steve, February 20, 2007 - 12:26 pm UTC

Does Oracle send an indicator to the client when a value is absent that would be interpreted as null? Or does Oracle send two column separaters (e.g. like ",," in a csv file)?

Also, what is the advantage in using emply_clob() (as opposed to leaving as null)? I've seen default values set as empty_clob() and am not sure of the reason.

Thanks,
Steve

February 20, 2007 - 2:59 pm UTC

the protocol is a bit more complicated than ',,'

yes, there would be a null flag.

As for the empty_clob() - it depends on the needs of the application - does it need an "empty file ready to accept data" or does it need "the absence of a file"

Why non-null requirement?

Steve, February 21, 2007 - 8:45 am UTC

Regarding empty_clob(), I should have read the LOB Developers Guide closer, it does explicitly state:
"Before you can start writing data to an internal LOB using the supported programmatic environment interfaces (PL/SQL, OCI, OCCI, Pro*C/C++, Pro*COBOL, Visual Basic, Java, or OLEDB), the LOB column/attribute must be made non-null, that is, it must contain a locator. You can accomplish this by initializing the internal LOB to empty in an INSERT/UPDATE statement using the functions EMPTY_BLOB() for BLOBs or EMPTY_CLOB() for CLOBs and NCLOBs."

But why is this? Isn't an insert an insert no matter who calls it (straight SQL vs. those listed above)? Does it have to do with obtaining the locator?

Thank you.
Steve

February 21, 2007 - 11:08 am UTC

the key thing here is "before you can start writing to a LOB using an API, you need something to write to"

You need a "file" - a lob locator - to write to

if you don't have one, you cannot programatically write to it.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer