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.

gimme_this_gimme_that@yahoo.com wrote:[color=blue]
> 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?[/color]

See the CLOB() function.

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.

gimme_this_gimme_that@yahoo.com wrote:[color=blue]
> 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.[/color]

You obviously did not read the documentation. Since you seem to have
trouble, here's the reference for the CLOB() function:
[color=blue][color=green]
>>-CLOB--(--character-string-expression--+------------+--)-----><[/color][/color]
'-,--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.

gimme_this_gimme_that@yahoo.com wrote:[color=blue]
> 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.
>[/color]
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

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