Forum Stats

  • 3,838,846 Users
  • 2,262,407 Discussions
  • 7,900,768 Comments

Discussions

Null CLOB value when inserting into table from trigger

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,605 Red Diamond
    edited Sep 8, 2018 8:02AM

    You are right on OP's code not handling NULLs, but as you said, "if one of the values is null, it won't insert", so it just can't be the cause of inserting null as new clob value. I bet what OP thinks is NULL is in fact EMPTY_CLOB():

    SQL> create table tbl(null_clob clob,empty_clob clob)

      2  /

    Table created.

    SQL> insert into tbl

      2  values(to_clob(null),empty_clob())

      3  /

    1 row created.

    SQL> select * from tbl

      2  /

    NULL_CLOB EMPTY_CLOB

    --------- ----------

    SQL>

    And if we can't even visualize visualize it because empty_clob() is locator to NULL string :

    SQL> set null >>>NULL<<<

    SQL> select * from tbl

      2  /

    NULL_CLOB  EMPTY_CLOB

    ---------- ----------

    >>>NULL<<< >>>NULL<<<

    SQL>

    But we can:

    SQL> select * from tbl where null_clob is null;

    NULL_CLOB  EMPTY_CLOB

    ---------- ----------

    >>>NULL<<< >>>NULL<<<

    SQL> select * from tbl where empty_clob is null;

    no rows selected

    SQL> select * from tbl where dbms_lob.compare(empty_clob,empty_clob()) = 0;

    NULL_CLOB  EMPTY_CLOB

    ---------- ----------

    >>>NULL<<< >>>NULL<<<

    SQL>

    SY.

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Sep 8, 2018 8:49AM

    Yes, I expressed I couldn't explain what he was saying happening, but highlighting there was definitely a problem with his logic. I had the same hunch it could be empty clob related too, but without some definitive information from OP, felt circumspect about it.

This discussion has been closed.