lob

Those pesky LONG columns

Connor McDonald , ,

There was a time, many moons ago Smile when CLOB, BLOB and BFILE did not exist as data types. So if you had anything longer than a few kilobytes of data to store, you had to use a LONG or a LONG RAW.  But those data types came with all sorts of restrictions and frustrations, and we all embraced the improvements that the LOB data types brought in Oracle 8.  But of course, we carry a lot of that historical “baggage” in the data dictionary.


SQL> select owner, table_name, column_name
  2  from   dba_tab_cols
  3  where  data_type = 'LONG'
  4  and    substr(table_name,1,4) in ('DBA_','ALL_','USER')
  5  order by 1,2, 3;

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ -------------------------
SYS                            ALL_ARGUMENTS                  DEFAULT_VALUE
SYS                            ALL_CLUSTER_HASH_EXPRESSIONS   HASH_EXPRESSION
SYS                            ALL_CONSTRAINTS                SEARCH_CONDITION
SYS                            ALL_IND_EXPRESSIONS            COLUMN_EXPRESSION
SYS                            ALL_IND_PARTITIONS             HIGH_VALUE
SYS                            ALL_IND_SUBPARTITIONS          HIGH_VALUE
SYS                            ALL_MVIEWS                     QUERY
SYS                            ALL_MVIEW_AGGREGATES           MEASURE
SYS                            ALL_MVIEW_ANALYSIS             QUERY
SYS                            ALL_NESTED_TABLE_COLS          DATA_DEFAULT
SYS                            ALL_REGISTERED_MVIEWS          QUERY_TXT
SYS                            ALL_REGISTERED_SNAPSHOTS       QUERY_TXT
SYS                            ALL_SNAPSHOTS                  QUERY
SYS                            ALL_SQLSET_PLANS               OTHER
SYS                            ALL_SUBPARTITION_TEMPLATES     HIGH_BOUND
SYS                            ALL_SUMMARIES                  QUERY
SYS                            ALL_SUMMARY_AGGREGATES         MEASURE
SYS                            ALL_TAB_COLS                   DATA_DEFAULT
SYS                            ALL_TAB_COLS_V$                DATA_DEFAULT
SYS                            ALL_TAB_COLUMNS                DATA_DEFAULT
SYS                            ALL_TAB_PARTITIONS             HIGH_VALUE
SYS                            ALL_TAB_SUBPARTITIONS          HIGH_VALUE
SYS                            ALL_TRIGGERS                   TRIGGER_BODY
SYS                            ALL_VIEWS                      TEXT
SYS                            ALL_VIEWS_AE                   TEXT
SYS                            ALL_ZONEMAPS                   QUERY
SYS                            ALL_ZONEMAP_MEASURES           MEASURE
SYS                            DBA_ADVISOR_SQLPLANS           OTHER
SYS                            DBA_ARGUMENTS                  DEFAULT_VALUE
SYS                            DBA_CLUSTER_HASH_EXPRESSIONS   HASH_EXPRESSION
SYS                            DBA_CONSTRAINTS                SEARCH_CONDITION
SYS                            DBA_IM_EXPRESSIONS             SQL_EXPRESSION
SYS                            DBA_IND_EXPRESSIONS            COLUMN_EXPRESSION
SYS                            DBA_IND_PARTITIONS             HIGH_VALUE
SYS                            DBA_IND_SUBPARTITIONS          HIGH_VALUE
SYS                            DBA_MVIEWS                     QUERY
SYS                            DBA_MVIEW_AGGREGATES           MEASURE
SYS                            DBA_MVIEW_ANALYSIS             QUERY
SYS                            DBA_NESTED_TABLE_COLS          DATA_DEFAULT
SYS                            DBA_OUTLINES                   SQL_TEXT
SYS                            DBA_REGISTERED_MVIEWS          QUERY_TXT
SYS                            DBA_REGISTERED_SNAPSHOTS       QUERY_TXT
SYS                            DBA_SNAPSHOTS                  QUERY
SYS                            DBA_SQLSET_PLANS               OTHER
SYS                            DBA_SQLTUNE_PLANS              OTHER
SYS                            DBA_SUBPARTITION_TEMPLATES     HIGH_BOUND
SYS                            DBA_SUMMARIES                  QUERY
SYS                            DBA_SUMMARY_AGGREGATES         MEASURE
SYS                            DBA_TAB_COLS                   DATA_DEFAULT
SYS                            DBA_TAB_COLS_V$                DATA_DEFAULT
SYS                            DBA_TAB_COLUMNS                DATA_DEFAULT
SYS                            DBA_TAB_PARTITIONS             HIGH_VALUE
SYS                            DBA_TAB_SUBPARTITIONS          HIGH_VALUE
SYS                            DBA_TRIGGERS                   TRIGGER_BODY
SYS                            DBA_VIEWS                      TEXT
SYS                            DBA_VIEWS_AE                   TEXT
SYS                            DBA_ZONEMAPS                   QUERY
SYS                            DBA_ZONEMAP_MEASURES           MEASURE
SYS                            USER_ADVISOR_SQLPLANS          OTHER
SYS                            USER_ARGUMENTS                 DEFAULT_VALUE
SYS                            USER_CLUSTER_HASH_EXPRESSIONS  HASH_EXPRESSION
SYS                            USER_CONSTRAINTS               SEARCH_CONDITION
SYS                            USER_IM_EXPRESSIONS            SQL_EXPRESSION
SYS                            USER_IND_EXPRESSIONS           COLUMN_EXPRESSION
SYS                            USER_IND_PARTITIONS            HIGH_VALUE
SYS                            USER_IND_SUBPARTITIONS         HIGH_VALUE
SYS                            USER_MVIEWS                    QUERY
SYS                            USER_MVIEW_AGGREGATES          MEASURE
SYS                            USER_MVIEW_ANALYSIS            QUERY
SYS                            USER_NESTED_TABLE_COLS         DATA_DEFAULT
SYS                            USER_OUTLINES                  SQL_TEXT
SYS                            USER_REGISTERED_MVIEWS         QUERY_TXT
SYS                            USER_REGISTERED_SNAPSHOTS      QUERY_TXT
SYS                            USER_SNAPSHOTS                 QUERY
SYS                            USER_SQLSET_PLANS              OTHER
SYS                            USER_SQLTUNE_PLANS             OTHER
SYS                            USER_SUBPARTITION_TEMPLATES    HIGH_BOUND
SYS                            USER_SUMMARIES                 QUERY
SYS                            USER_SUMMARY_AGGREGATES        MEASURE
SYS                            USER_TAB_COLS                  DATA_DEFAULT
SYS                            USER_TAB_COLS_V$               DATA_DEFAULT
SYS                            USER_TAB_COLUMNS               DATA_DEFAULT
SYS                            USER_TAB_PARTITIONS            HIGH_VALUE
SYS                            USER_TAB_SUBPARTITIONS         HIGH_VALUE
SYS                            USER_TRIGGERS                  TRIGGER_BODY
SYS                            USER_VIEWS                     TEXT
SYS                            USER_VIEWS_AE                  TEXT
SYS                            USER_ZONEMAPS                  QUERY
SYS                            USER_ZONEMAP_MEASURES          MEASURE
WMSYS                          ALL_WM_IND_EXPRESSIONS         COLUMN_EXPRESSION
WMSYS                          USER_WM_IND_EXPRESSIONS        COLUMN_EXPRESSION

So when we want to do some data mining against the dictionary, the following error is a common and frustrating one!


SQL> select *
  2  from   dba_views
  3  where  text like '%mystring%';
where  text like '%mystring%'
       *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

I often hear the comment “Why not just in the next version, just change all those LONG’s to CLOB?”.  I imagine it would be possible to do so, but just pause for a second and think of the amount of regression testing that would need to occur, not just inside the database group in the Oracle organization, but for every customer that has ever coded up a mechanism in PL/SQL, or C, or Java, or any other language that is potentially using a LONG column in one of their queries.  I don’t know for sure, but I suspect that is the reason why we have added new columns to the dictionary rather than modify existing ones.  For example, if you look at DBA_VIEWS, you can see the addition of a column TEXT_VC which is a VARCHAR2 equivalent to the pre-existing TEXT column which is a LONG.


SQL> desc DBA_VIEWS
 Name                                Null?    Type
 ----------------------------------- -------- ----------------
 OWNER                               NOT NULL VARCHAR2(128)
 VIEW_NAME                           NOT NULL VARCHAR2(128)
 TEXT_LENGTH                                  NUMBER
 TEXT                                         LONG
 TEXT_VC                                      VARCHAR2(4000)
 TYPE_TEXT_LENGTH                             NUMBER
 TYPE_TEXT                                    VARCHAR2(4000)
 OID_TEXT_LENGTH                              NUMBER
 OID_TEXT                                     VARCHAR2(4000)
 VIEW_TYPE_OWNER                              VARCHAR2(128)
 VIEW_TYPE                                    VARCHAR2(128)
 SUPERVIEW_NAME                               VARCHAR2(128)
 EDITIONING_VIEW                              VARCHAR2(1)
 READ_ONLY                                    VARCHAR2(1)
 CONTAINER_DATA                               VARCHAR2(1)
 BEQUEATH                                     VARCHAR2(12)
 ORIGIN_CON_ID                                NUMBER
 DEFAULT_COLLATION                            VARCHAR2(100)
 CONTAINERS_DEFAULT                           VARCHAR2(3)
 CONTAINER_MAP                                VARCHAR2(3)
 EXTENDED_DATA_LINK                           VARCHAR2(3)
 EXTENDED_DATA_LINK_MAP                       VARCHAR2(3)

TEXT_VC is a nice touch, but it would have been nice to have that as a CLOB column to get access to the entire DDL for the view should it exceed 4000 characters.  I can’t solve that problem, but I thought perhaps I can give you a workable compromise.  In 8i, the TO_LOB function was implemented to allow customers to migrate from LONG to CLOB with minimal fuss.  I can take advantage of that to provide a CLOB-based copy of DBA_VIEWS:


SQL> create table system.t  as
  2  select
  3     owner
  4    ,view_name
  5    ,text_length
  6    ,to_lob(text) text
  7    ,text_vc
  8    ,type_text_length
  9    ,type_text
 10    ,oid_text_length
 11    ,oid_text
 12    ,view_type_owner
 13    ,view_type
 14    ,superview_name
 15    ,editioning_view
 16    ,read_only
 17    ,container_data
 18    ,bequeath
 19    ,origin_con_id
 20    ,default_collation
 21    ,containers_default
 22    ,container_map
 23    ,extended_data_link
 24    ,extended_data_link_map
 25  from dba_views;

Table created.

SQL>
SQL> create index system.t_ix on system.t ( owner, view_name );

Index created.

That is all well and good, but of course, the moment I perform maintenance on any view in the system, that table will be “stale”. That still might be a perfectly reasonably solution for you – you just refresh the table contents (say) once per day, or when you do deployments of schema changes into your database.  But perhaps we can do a little better than that.  Using a DDL event trigger, we can capture changes on views and adjust our copy accordingly.


SQL> create or replace
  2  trigger sys.view$clob$handler
  3  after create or alter or drop or rename
  4  on database
  5  when ( ora_dict_obj_type = 'VIEW' )
  6  declare
  7    l_obj_name  varchar2(128) := ora_dict_obj_name;
  8    l_obj_owner varchar2(128) := ora_dict_obj_owner;
  9
 10    l_text   varchar2(1000);
 11    sql_text ora_name_list_t;
 12    l_idx    pls_integer;
 13  begin
 14    lock table system.t in exclusive mode;
 15
 16    --
 17    -- remove the existing row for the view
 18    --
 19    delete from system.t where owner = l_obj_owner and view_name = l_obj_name;
 20
 21    --
 22    -- if it is a rename event, we will try to derive the new name
 23    -- from the sql statement by looking for a trailing "TO"
 24    --
 25    if ora_sysevent in ('RENAME') then
 26      l_idx := ora_sql_txt(sql_text);
 27      for i in 1 .. l_idx
 28      loop
 29          l_text := l_text || sql_text(i);
 30      end loop;
 31      l_idx := instr(lower(l_text),' to ');
 32      if l_idx = 0 then
 33        raise_application_error(-20000,'Could not find appropriate rename content');
 34      end if;
 35      --
 36      -- The SQL has a trailing chr(0) which we need to remove
 37      --
 38      l_text := rtrim(ltrim(substr(l_text,l_idx+4)),' '||chr(0));
 39      --
 40      -- The object name in quotes mean we preserve the case specified, otherwise
 41      -- we will normalize to upper
 42      --
 43      if l_text like '"%"' then
 44        l_obj_name := rtrim(ltrim(l_text,'"'),'"');
 45      else
 46        l_obj_name := upper(l_text);
 47      end if;
 48    end if;
 49
 50    --
 51    -- Now we insert the updated definition for the view, or
 52    -- perhaps its new name
 53    --
 54    if ora_sysevent in ('CREATE','ALTER','RENAME') then
 55      insert into system.t
 56      select
 57         owner
 58        ,view_name
 59        ,text_length
 60        ,to_lob(text) text
 61        ,text_vc
 62        ,type_text_length
 63        ,type_text
 64        ,oid_text_length
 65        ,oid_text
 66        ,view_type_owner
 67        ,view_type
 68        ,superview_name
 69        ,editioning_view
 70        ,read_only
 71        ,container_data
 72        ,bequeath
 73        ,origin_con_id
 74        ,default_collation
 75        ,containers_default
 76        ,container_map
 77        ,extended_data_link
 78        ,extended_data_link_map
 79      from dba_views
 80      where owner = l_obj_owner
 81      and   view_name = l_obj_name;
 82    end if;
 83
 84  end;
 85  /

Trigger created.

Obviously, for more serious usage you’ll be choosing a better table name and not using SYS, but this is just a demo on my laptop.  Unlike a DML trigger, where we have access to “new” and “old” images of the data, for a RENAME command, I had to probe the SQL text to try derive the new name. So lets now perform some view DDL and see how our tracking trigger accommodates the changes.


SQL> create view view1 as select * from ALL_objects;

View created.

SQL> create view view2 as select * from ALL_objects;

View created.

SQL> create view view3 as select * from view2;

View created.

SQL> drop view view1;

View dropped.

SQL> rename view2 to view2a;

Table renamed.

SQL>
SQL>
SQL> select count(*) from system.t;

  COUNT(*)
----------
      7347

SQL> select count(*) from dba_views;

  COUNT(*)
----------
      7347

SQL> select owner, view_name from dba_views
  2  minus
  3  select owner, view_name from system.t;

no rows selected

SQL>
SQL> select owner, view_name from system.t
  2  minus
  3  select owner, view_name from dba_views;

no rows selected

And there we have it – our own custom version of DBA_VIEWS where the TEXT is now exposed as a CLOB column.  So now, mining that column for information is as easy as a simple predicate


SQL> select owner, view_name
  2  from   system.t
  3  where  text like 'selec%ALL_objects';

OWNER                          VIEW_NAME
------------------------------ -------------------
MCDONAC                        VIEW2A

LOBs from afar

Connor McDonald , ,

This has always been a nuisance.  There you are – getting all the bells and whistles with LOBs…until a database link enters the room Smile


--
-- Database: DB11
--
SQL> create table t ( id int, c clob );

Table created.

SQL> insert into t values (1,rpad('x',32000,'x'));

1 row created.

SQL> select * 
  2  from   t;

        ID C
---------- --------------------------------------------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

--
-- Database: anywhere except DB11
--
SQL> select * 
  2  from   t@db11;

ERROR:
ORA-22992: cannot use LOB locators selected from remote tables

 

Well that sucks. But look what has snuck its way into 12.2 !


SQL> select * 
  2  from   t@db122;

        ID C
---------- --------------------------------------------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

There is a whole chapter on it here in the official docs.  Very nice indeed.

LOBs and tiny typos

Connor McDonald , , , ,

This one caught me out – I was racing around looking for bugs, or parameter files, or hidden settings that stopped SECUREFILE lobs from being created.  Here was my incredibly simple test case – create a securefile LOB, and then make sure it’s a securefile.


SQL> create table t1 ( b blob ) lob ( b ) store as securfile;

Table created.

SQL> select securefile
  2  from   user_lobs
  3  where  table_name = 'T1';

SEC
---
NO

That had me bamboozled, but it is a trivial explanation.  Notice that I did not spell  “SECUREFILE” correctly.  As a result, the syntax is interpreted as being the name of the LOB segment in the data dictionary, rather than the specification of how the LOB should be stored.


SQL> select segment_name
  2  from   user_lobs
  3  where  table_name = 'T1';

SEGMENT_NAME
------------------------------
SECURFILE

All it takes is the correct “e” in “securefile” and normal service was resumed Smile


SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 ( b blob ) lob ( b ) store as securefile;

Table created.

SQL> select securefile
  2  from   user_lobs
  3  where  table_name = 'T1';

SEC
---
YES

So why blog about it such a silly mistake ?  Because this is a common issue with all of us as developers.  We see something unusual and our first assumption is that it must be some fundamental flaw in the product we’re using.  That mindset has been around as long computer programming has existed, but but 20 years ago, it wasn’t so easy to make a fool of yourself by bleating about it on social media Smile.  I remember when I first started programming, one of my mentors told me: “Remember, the COBOL compiler is not broken”, and of course, we can insert any programming language into that sentence.  So before you jump on to Twitter … just take a moment to re-check that script, or get a colleague to give it a “once over”.  You’ll either save yourself some embarrassment, or you add additional rigour to your test case – it’s win-win.

Securefile in 12c – part 2

Connor McDonald , , ,

In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c.  Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile LOBs defined.  This blog post explores that.

We’ll start with “PERMITTED” mode, and create a table with two partitions. We can see that at table level and partition level, the LOBs are stored as BASICFILE because we have explicitly create them as such. Can we add a new partition but opt to have SECUREFILE for that partition ?



SQL> alter session set db_securefile = permitted;

Session altered.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T ( x int, y clob )
  2  lob(y) store as basicfile
  3  partition by list ( x )
  4  (
  5    partition p1 values (1),
  6    partition p2 values (2)
  7  )
  8  /

Table created.

SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO

2 rows selected.

SQL>
SQL> alter table T
  2   add partition p3 values (3)
  3   lob(y) store as securefile
  4  /

Table altered.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO
P3                   YES

3 rows selected.

OK…no dramas there. We can see that the new partition will be SECUREFILE whilst the older ones are left untouched. Let’s now reset out db_securefile parameter



SQL> alter session set db_securefile = preferred;

Session altered.

SQL> alter table T
  2   add partition p4 values (4)
  3  /

Table altered.

SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO
P3                   YES
P4                   YES

We can see that with the parameter set to “preferred” (the default in 12c) that any new partitions will be defined as SECUREFILE even though the table level default remains at BASICFILE. This is alluded to within the reference documentation for db_securefile

PREFERRED

All LOBs are created as SecureFiles unless BASICFILE is explicitly specified in the LOB storage clause or the tablespace is a Manual Segment Space Management tablespace. When PREFERRED is set, cases where BASICFILE would otherwise be inherited from the partition or column level LOB storage are ignored; the LOBs will be created as SecureFiles instead.

One interesting aspect of this, is that even at table creation time, the partition creation can be conceptualised as “create table, then add each partition”, because look what happens in that case (with our default setting of “preferred”)


SQL> alter session set db_securefile = preferred;

Session altered.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T ( x int, y clob )
  2  lob(y) store as basicfile
  3  partition by list ( x )
  4  (
  5    partition p1 values (1),
  6    partition p2 values (2)
  7  )
  8  /

Table created.

SQL>
SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   YES
P2                   YES

2 rows selected.

Even those partitions nominated at table creation time are still defaulted to SECUREFILE.

Loading LOB from a file

Connor McDonald ,

I observed this idiosyncracy recently when loading some lob from external files using PL/SQL:

First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size



SQL> !echo "This is line 1" > /tmp/lobfile
SQL> !echo "This is line 2" >> /tmp/lobfile
SQL> !echo "This is line 3" >> /tmp/lobfile
SQL> !echo "This is line 4" >> /tmp/lobfile
SQL> !echo "This is line 5" >> /tmp/lobfile
SQL> !wc /tmp/lobfile
       5      20      75 /tmp/lobfile

Then create a standard routine to load it into the database


SQL> create or replace directory TMP as '/tmp';

Directory created.

SQL> drop table lob_tab;

Table dropped.

SQL> create table lob_tab ( the_lob clob );

Table created.

SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    amt       number;
  5  begin
  6    l_bfile := bfilename( 'TMP', 'lobfile' );
  7
  8    insert into lob_tab (the_lob)
  9    values ( empty_clob() )
 10    returning the_lob into v_lob;
 11
 12    amt := dbms_lob.getlength( l_bfile );
 13    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 14    dbms_lob.loadfromfile( v_lob, l_bfile ,amt);
 15
 16    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 17    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 18    dbms_lob.fileclose( l_bfile );
 19  end;
 20  /
File length is: 75
Loaded length is: 37   <==== !!!!

PL/SQL procedure successfully completed.

So what has happened to the second half of the LOB ?!

It turns out to be related to the character set. If you’re using UTF8 (as this database is), then loadfromfile must assume the possibility of multibyte characters (since the bfile could be binary). Thus you get two bytes per character and hence “garbage” in the lob.

The workaround is to use sqlldr, or load the lob without using loadfromfile, eg you could load it piecewise:


SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    buf       raw(32767);
  5    vc        varchar2(32767);
  6    amt       number;
  7    v_offset  number := 1;
  8  begin
  9    l_bfile := bfilename( 'TMP', 'lobfile' );
 10
 11    insert into lob_tab (the_lob)
 12    values ( empty_clob() )
 13    returning the_lob into v_lob;
 14
 15    amt := dbms_lob.getlength( l_bfile );
 16    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 17    dbms_lob.read(l_bfile,amt,v_offset,buf);
 18    vc := utl_raw.cast_to_varchar2(buf);
 19    dbms_lob.writeappend(v_lob,amt,vc);
 20
 21    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 22    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 23    dbms_lob.fileclose( l_bfile );
 24  end;
 25  /
File length is: 75
Loaded length is: 75

PL/SQL procedure successfully completed.

Or you could use LOADCLOBFROMFILE with the extra parameters


SQL> declare
  2    v_lob     clob;
  3    l_bfile   bfile;
  4    amt       number;
  5
  6    d_offset number := 1;
  7    s_offset number := 1;
  8    csid     number := 0;
  9    lang     number := 0;
 10    warning  number;
 11  begin
 12    l_bfile := bfilename( 'TMP', 'lobfile' );
 13
 14    insert into lob_tab (the_lob)
 15    values ( empty_clob() )
 16    returning the_lob into v_lob;
 17
 18    amt := dbms_lob.getlength( l_bfile );
 19    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
 20    dbms_lob.LOADCLOBFROMFILE( v_lob, l_bfile ,amt, d_offset,s_offset,csid, lang,warning );
 21
 22    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
 23    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
 24    dbms_lob.fileclose( l_bfile );
 25  end;
 26  /
File length is: 75
Loaded length is: 75

PL/SQL procedure successfully completed.

It might look like a bug, but it’s not really – if you look carefully, you’ll see that this behaviour is documented in the DBMS_LOB manual.

Converting LONG to CLOB

Connor McDonald ,

Some folks still are stuck with LONG columns, and are keen to move to LOB.  Since version 9, we’ve had a nice facility to do that – just with a simple alter command.

You can now simply issue “alter table (longcol CLOB)” to perform the conversion. This is a neat tool, but be aware of the space implications before attempting a conversion.

Here is a simple example to demonstrate:



-- 
-- First a very small tablespace so we can monitor temporary space requirements
--

SQL> create tablespace DEMO datafile 'C:\ORACLE\ORADATA\NP12\DEMO01.DBF' size 1m
  2  autoextend on next 256k
  3  extent management local uniform size 128k;

Tablespace created.

SQL> alter user mcdonac default tablespace DEMO;

User altered.


-- 
-- Now a basic table with a LONG, we'll load some data from DBA_VIEWS
--

SQL> create table testlong ( text long );

Table created.

SQL> declare
  2      p varchar2(32767);
  3  begin
  4   for j in 1 .. 10 loop
  5     for i in ( select text from dba_views where text_length < 32500 ) loop
  6        p := i.text;
  7       insert into testlong values (p);
  8     end loop;
  9     commit;
 10   end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','TESTLONG')

PL/SQL procedure successfully completed.

SQL> col bytes format 999,999,999,999
SQL> col name format a60
SQL> set lines 120
SQL> select bytes, name
  2  from v$datafile
  3  where name like '%DEMO01%';

           BYTES NAME
---------------- ------------------------------------------------------------
      80,216,064 C:\ORACLE\ORADATA\NP12\DEMO01.DBF


SQL> select bytes from user_segments
  2  where tablespace_name = 'DEMO';

           BYTES
----------------
      80,084,992

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN,CHAIN_CNT
  2  from user_tables
  3  where table_name = 'TESTLONG';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ------------ ----------- ----------
     68130       9633            0           0          0

So as you can see, the table in its current form is using about 80M of space and the tablespace has grown to match. Now we convert that column to a clob



SQL> alter table TESTLONG modify ( text clob);

Table altered.

SQL> col segment_name format a30

SQL> select segment_name, bytes
  2  from user_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                              BYTES
------------------------------ ----------------
TESTLONG                             49,938,432
SYS_IL0000105876C00001$$                131,072
SYS_LOB0000105876C00001$$            42,074,112

So we have some additional segments (as we would expect) and the amount of space used has grown by about 10%. This seems pretty perfectable good. But take a look at the tablespace


SQL> select bytes, name
  2  from v$datafile
  3  where name like '%DEMO%';

           BYTES NAME
---------------- ------------------------------------------------------------
     172,490,752 C:\ORACLE\ORADATA\NP12\DEMO01.DBF


SQL>

If we look at the tablespace, it has grown to 170M so at least temporarily, over double the space was required. I’m not saying that this is a bad thing – but its certainly something to be aware of when you doing CLOB conversions

CLOBs are not just big varchars

Connor McDonald ,

We had a question on AskTom the other day, talking about comparing clobs.  (Paraphrasing) the question inquired as to why we had a DBMS_LOB.COMPARE function, when you can you just compare clobs directly, using the following example:


SQL> create table T ( x clob, y clob );

Table created.

SQL> create or replace
  2  trigger TRG before insert or update on T
  3  for each row
  4  begin
  5    if :new.x != :new.y then
  6       raise_application_error(-20000,'error');
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into T values ('qwe','qwe');

1 row created.

SQL>
SQL> update T set y = 'abc';
update T set y = 'abc'
       *
ERROR at line 1:
ORA-20000: error
ORA-06512: at "TRG", line 3
ORA-04088: error during execution of trigger 'MCDONAC.TRG'

You can see that we’ve created a table, and added a trigger to make sure the two clob columns are equal. We’ve done what seems to be all the tests required, namely, test for equality and test for inequality. But the example masks that this is in fact a special case, namely, that we are creating/replacing a clob in its entirety. And that is not the only way we can manage clobs. Here is why you need to be super careful when using LOB’s within triggers.


SQL> declare
  2    c clob;
  3  begin
  4    select y into c from t for update;
  5    dbms_lob.writeappend(c,100,rpad('x',100,'x'));
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from T;

X
--------------------------------------------------------------------------------
Y
--------------------------------------------------------------------------------
qwe
qwexxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxx

The DBMS_LOB routines (such as “writeappend”) allow modification of a clob’s content (in fact, its the normal way of doing so).  And notice that modifying the clob is not the same as updating the clob. The trigger has not fired, and hence our rule of “x” being equal to “y” is trivially broken.