By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Seek equivalent of Oracle's empty_clob() for CLOB creation

P: n/a
In Oracle you can have a statement such as

insert into foo (foo_id,some_clob) values (100,empty_clob())

where empty_clob() inserts a clob address.
What is the approach in DB2 (8.1) to creating a row
having with a CLOB and no contents but with no null?

If someone has a user defined method that does about
the same thing that would be a help.

Thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Ian
gi*******************@yahoo.com wrote:
In Oracle you can have a statement such as

insert into foo (foo_id,some_clob) values (100,empty_clob())

where empty_clob() inserts a clob address.
What is the approach in DB2 (8.1) to creating a row
having with a CLOB and no contents but with no null?


See the CLOB() function.

Nov 12 '05 #2

P: n/a
Good idea ...

insert into foo (foo_id,some_clob) values (100,clob())

Returns :

No authorized routine name "CLOB" of type "FUNCTION" having compatible
argu

Having reviewed DB2 Documentation nothing came up on CLOB() or CLOB
FUNCTION.

Nov 12 '05 #3

P: n/a
Ian
gi*******************@yahoo.com wrote:
Good idea ...

insert into foo (foo_id,some_clob) values (100,clob())

Returns :

No authorized routine name "CLOB" of type "FUNCTION" having compatible
argu

Having reviewed DB2 Documentation nothing came up on CLOB() or CLOB
FUNCTION.


You obviously did not read the documentation. Since you seem to have
trouble, here's the reference for the CLOB() function:
-CLOB--(--character-string-expression--+------------+--)-----><

'-,--integer-'
The schema is SYSIBM.

The CLOB function returns a CLOB representation of a character string
type. In a Unicode database, if a supplied argument is a graphic string,
it is first converted to a character string before the function is executed.

character-string-expression
An expression that returns a value that is a character string.

integer
An integer value specifying the length attribute of the resulting
CLOB data type. The value must be between 0 and 2 147 483 647. If
integer is not specified, the length of the result is the same as the
length of the first argument.

The result of the function is a CLOB. If the argument can be null, the
result can be null; if the argument is null, the result is the null value.

This topic can be found in: SQL Reference, Volume 1.
Nov 12 '05 #4

P: n/a
gi*******************@yahoo.com wrote:
In Oracle you can have a statement such as

insert into foo (foo_id,some_clob) values (100,empty_clob())

where empty_clob() inserts a clob address.
What is the approach in DB2 (8.1) to creating a row
having with a CLOB and no contents but with no null?

If someone has a user defined method that does about
the same thing that would be a help.

Thanks.

Just use an empty string ('') DB2 will cast it it up as appropriate.
But if you want a function:

CREATE FUNCTION CLOB() RETURNS CLOB(10M)
NO EXTERNAL ACTION DETERMINISTIC CONTAINS SQL
RETURN ''

Note that in DB2 and empty string/LOB and NULL completely different.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

P: n/a
Thanks Serge and Ian. Without you guys it would be a lot harder to get
things done.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.