Re: Inserting NULL into BLOB / LONG RAW

From: Jack C. Applewhite <Jack.Applewhite_at_sbti.com>
Date: Mon, 06 Nov 2000 17:43:19 -0600
Message-Id: <10672.121259@fatcity.com>

Helmut Daiminger wrote:

> Hi!
>
> Is it a problem inserting NULL into BLOB or LONG RAW columns? The colums were
> defined to allow NULL values. We are experiencing difficulties inserting into a
> table after the colum has been changed vom LONG RAW to BLOB.
>
> Are NULLs handled differently in those data types? This is 8.1.6
>
> Thanks,
> Helmut

You can initialize a LOB to Null, but it causes problems later if you try to use the DBMS_LOB package to populate the LOB column(s). Below is from Chapter 2 of the 8.1.6. "Oracle8i Application Developer's Guide - Large Objects (LOBs)":

...
Initializing Internal LOBs to NULL or Empty

You can set an internal LOB -- that is, a LOB column in a table, or a LOB attribute in an object type defined by you-- to be NULL or empty:

Setting an Internal LOB to NULL: A LOB set to NULL has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other datatypes.

Setting an Internal LOB to Empty: By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, you get back a locator which you can use to populate the LOB with data via one of the six programmatic environments, such as OCI or PL/SQL(DBMS_LOB). See Chapter 3, "LOB Programmatic Environments". ...

Hope this helps.

Jack

--
Jack C. Applewhite
Senior Consultant, OCP Oracle8 DBA
Stonebridge Technologies, Inc.
...The Fast Track to e-Business.
  (visit us at www.sbti.com)

Received on Mon Nov 06 2000 - 17:43:19 CST