Fix inserting empty CLOB, BLOB and RAW values

[intgr]

Inserting empty strings to CLOB, BLOB, RAW and LONG RAW fields would fail with "ORA-01459: invalid length for variable character string".

Oracle considers empty values equivalent to NULL, so we should insert NULL instead.

[laurenz]

I'll take a look when I return from vacation. But as far as I remember, there are empty LOBs in Oracle. We should use that rather than NULL.

[intgr]

But as far as I remember, there are empty LOBs in Oracle.

Ah I can try and check later it if I remember.

[intgr]

But as far as I remember, there are empty LOBs in Oracle.

I tested this, looks like all empty values are equivalent to NULL.

CREATE TABLE typetest1 (
    id  NUMBER(5) CONSTRAINT typetest1_pkey PRIMARY KEY,
    c   CHAR(10 CHAR),
    nc  NCHAR(10),
    vc  VARCHAR2(10 CHAR),
    nvc NVARCHAR2(10),
    lc  CLOB,
    r   RAW(10),
    u   RAW(16),
    lb  BLOB,
    lr  LONG RAW
) SEGMENT CREATION IMMEDIATE;

insert into typetest1 values (
    1, '', '', '', '', '', '', '', '', ''
);

-- All of the following queries return 1 row:
select * from typetest1 where c is null;
select * from typetest1 where nc is null;
select * from typetest1 where vc is null;
select * from typetest1 where nvc is null;
select * from typetest1 where lc is null;
select * from typetest1 where r is null;
select * from typetest1 where u is null;
select * from typetest1 where lb is null;
select * from typetest1 where lr is null;

[laurenz]

Try this in Oracle:

CREATE TABLE nclob (
   id number(5) CONSTRAINT nclob_pkey PRIMARY KEY,
   c clob,
   nc nclob
);

INSERT INTO nclob VALUES (1, EMPTY_CLOB(), EMPTY_CLOB());

COMMIT;

That will insert an empty LOB that is not NULL:

SELECT CASE WHEN c IS NULL
            THEN 'IS NULL'
            ELSE 'IS NOT NULL'
       END AS test
FROM nclob;

TEST
-----------
IS NOT NULL

SELECT length(c) AS length FROM nclob;

    LENGTH
----------
	 0

So while empty strings and NULL are the same for VARCHAR2 columns, they are not the same for CLOBs. Wouldn't you say that an empty CLOB is a better value for an empty string?

[intgr]

Oh, right you are. I'm surprised that inserting '' has different behavior. But if empty LOB values are valid then we should support them as well.

I'll have to dig through the OCI documentation about how to bind empty LOB values.

[intgr]

My oracle_fdw project has been on the backburner lately, but I will revisit this.

[laurenz]

No worries, and thanks for the update.

[laurenz]

This has been fixed in 8d73c2cd94a29dce7876c282d98737a71cad4477, see bug #679.

My fix is not really clean: I bind empty strings as NULL values. That is technically wrong, because you can have empty LOBs, but I figured it would be too painful to bind proper empty LOBs. Looking at your patch, you did quite the same.

So shame on me for letting this pull request rot; it would have saved me some thinking.

[laurenz]

And thank you for your contribution!