inserting string expression into LOB column does not work

[Former Member]

With the following test table:

create table test (nr numeric(8), txt CLOB)

This works:

insert into test values (1, 'ABCDEF')

and this does not:

insert into test values(1, 'ABC' || 'DEF')

Error in assignment;-8004 POS(34) Constant must be compatible with column type and length

At first i thought the cause was that the concatenated expression is ASCII type and the column is UNICODE.

But on a table like this:

create table test (nr numeric(8), txt CLOB ASCII)

the same problem occurs..

Is this a bug or a limitation in MaxDB?

In the latter case: what are the exact limitations on CLOB insert's?

[Former Member · Accepted solution]

Hi,

usually insert..select works even with LOB-values.

Therefore I would like to know:

1. the version of the kernel you are using

2. what you really WANT to do, not how you tried it and did not succeed. Perhaps we can tell you how you SHOULD do it.

3. the table definitions of the current try: meaning the table definitions for the table to be inserted in, the definition for the table you select from and the insert..select itself.

Elke

[Former Member · Accepted solution]

Well our objective: we have a "message" table here.

This table contains (among other columns) a CLOB column (unicode) with the message XML.

We want to insert (generate) some messages in this table based on a query result.

Just using SQL.

The message XML is constructed from fixed (literal) parts & column values

retrieved from 1 or more tables. That's why the concatenate was used.

We also tried this method:

create table test (nr numeric(8), txt CLOB)

insert into test (nr, txt)

select 1, 'AA'||'BB' from dual

But again: Integrity constraint violation;-8006 POS(1) Data types must be compatible:TXT

MaxDB version 7.7.006

That was the problem I was referring to.

[Former Member · Accepted solution]

We solved this issue by generating plain insert statements with a query

and writing those to a file.

Then executing the file.

This works, but the query can get very nasty due to nested quoting etc.

[Former Member · Accepted solution]

The insert..select problem I elaborated above : can you confirm?

[Former Member]

Ok thx situation (limitation) is clear

[Former Member]

Hi,

as I said, with plain insert you will need a string literal or a parameter for inserting into a LOB-value,

With insert...select there is only a selected lob-value possible. Not even string literals will help there and value expressions will not do either.

Yes, I confirm the reaction for this statement.

But I have to tell you that there are no plans to change (extend the possibilities for) inserts into lobs.

You will have to use that query-something, you mentioned. I did not catch your sentence with the many quotes making the query nasty, but if you want to concat the value for a lob, you cannot do it with the help of the database.

Elke

[Former Member]

The insert..select problem I elaborated above : is it possible to confirm that?

[Former Member]

Hi,

this is a limitation, no bug.

No expressions (like concat, rfill (..) or the like are allowed, just '.....' or parameter.

If you insert with string-literals into lobs, then the sum of bytes inserted as string literals into all LOBs of ONE record is limited to something around 8000 bytes.

But as one very rarely will type in as many character, this should be no real restriction.

Usually lobs are filled with parameter values. And for them there is no restriction (ok, ok, 2GB bytes per single LOB-value is the restriction)

Elke

[Former Member]

Thanks Elke for your answer.

I can add that also

INSERT INTO xxx as

SELECT ....

also will not work when xxx has a LOB column.