MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle LOB Storage Syntax

with 4 comments

Somebody asked me to post some examples of Large Object syntax. I checked Chapter 9 of the Oracle Database SecureFiles and Large Object Developer’s Guide, and I agree an example or two would be helpful. I’ve provided CREATE and ALTER statements.

Here’s a sample CREATE TABLE statement for a CLOB, BLOB, and BFILE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

CREATE TABLE item
( item_id             NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_barcode        VARCHAR2(20)  CONSTRAINT nn_item_1  NOT NULL
, item_type           NUMBER        CONSTRAINT nn_item_2  NOT NULL
, item_title          VARCHAR2(60)  CONSTRAINT nn_item_3  NOT NULL
, item_subtitle       VARCHAR2(60) 
, item_desc           CLOB          CONSTRAINT nn_item_4  NOT NULL
, item_blob           BLOB
, item_photo          BFILE
, item_rating         VARCHAR2(8)   CONSTRAINT nn_item_5  NOT NULL
, item_rating_agency  VARCHAR2(4)   CONSTRAINT nn_item_6  NOT NULL
, item_release_date   DATE          CONSTRAINT nn_item_7  NOT NULL
, created_by          NUMBER        CONSTRAINT nn_item_8  NOT NULL
, creation_date       DATE          CONSTRAINT nn_item_9  NOT NULL
, last_updated_by     NUMBER        CONSTRAINT nn_item_10 NOT NULL
, last_update_date    DATE          CONSTRAINT nn_item_11 NOT NULL
, CONSTRAINT fk_item_1                      FOREIGN KEY(item_type)
  REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_item_2                      FOREIGN KEY(created_by)
  REFERENCES system_user(system_user_id)
, CONSTRAINT fk_item_3                      FOREIGN KEY(last_updated_by)
  REFERENCES system_user(system_user_id))
  LOB (item_desc) STORE AS BASICFILE item_desc
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645))  
, LOB (item_blob) STORE AS item_blob
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645));

If you attempt to make the CHUNK size greater than 32,768, it will raise an ORA-22851: invalid CHUNK LOB storage option value error.

Here’s a sample ALTER TABLE statement that adds a new large object column to a table. You can’t modify an existing large object column. You raise an ORA-22859: invalid modification of columns error when you try it. If you need to make modifications, check this post form last year on re-organizing LOB indexes.

1
2
3
4
5
6
7
8

ALTER TABLE item ADD (another CLOB)
LOB (another) STORE AS BASICFILE item_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT 1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));

This all makes sense, but hold on there’s a potential problem when you add a large object column to a table. I’m not sure it’s a bug but I’ll probably open a TAR on it later in the week (arghhhh! I once worked in Oracle Support. :-)). Anyway, here’s what I found:

If you drop and recreate the table, you can encounter an ORA-01430 error. It appears that the large object is really hanging out in the catalog. As soon as you try to re-add it, you get the error.

If you describe the table after recreating it, you’ll see the following:

SQL> DESCRIBE item
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ITEM_ID                                   NOT NULL NUMBER
 ITEM_BARCODE                              NOT NULL VARCHAR2(20)
 ITEM_TYPE                                 NOT NULL NUMBER
 ITEM_TITLE                                NOT NULL VARCHAR2(60)
 ITEM_SUBTITLE                                      VARCHAR2(60)
 ITEM_DESC                                 NOT NULL CLOB
 ITEM_BLOB                                          BLOB
 ITEM_PHOTO                                         BINARY FILE LOB
 ITEM_RATING                               NOT NULL VARCHAR2(8)
 ITEM_RATING_AGENCY                        NOT NULL VARCHAR2(4)
 ITEM_RELEASE_DATE                         NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER
 LAST_UPDATE_DATE                          NOT NULL DATE

Also, if you run the following query with the DBMS_METADATA package, the column doesn’t exist in the table defintion:

SELECT dbms_metadata.get_ddl('TABLE','ITEM') FROM dual;

If try to run the ALTER statement to add the column that doesn’t appear to exist, you’ll get the following message in Oracle 11.1.0.7.0:

ALTER TABLE item ADD (another CLOB)
                      *
ERROR at line 1:
ORA-01430: COLUMN being added already EXISTS IN TABLE

If you rerun the DBMS_METADATA.GET_DDL query, it’ll be there in the table definition. Also, in its non-existence but existent state (ghost-state), there’s nothing in the DBA_LOB, or DBA_SEGMENT views. You can find a query to check large object segments and indexes here. My questions is where has it gone, why is it there, and did I do something wrong because this looks like basic functionality.

If you know the mystery or the existence of a bug, please post a comment.

Written by maclochlainn

July 17th, 2009 at 12:40 am

Posted in clob,Oracle,sql

«
»