Sakai's auto DDL code creates deprecated data types (LONG and LONG RAW) in Oracle databases. While Sakai continues to work, the use of these deprecated data types can cause problems for other tools that work with the database, including Oracle's own maintenance tools. Conversion of these data types to CLOB and BLOB is recommended. This script wi…

convert-longs-to-lobs.sql

-- convert-longs-to-lobs.sql
--   Scan for and convert LONG and LONG RAW fields in a Sakai database
--   See https://jira.sakaiproject.org/browse/SAK-7977
--
-- Created 2013-03-29 daveadams@gmail.com
--
-- USAGE:
--  To scan for LONG and LONG RAW fields, but make no changes:
--    $ sqlplus -S dbuser/dbpass@dbsid @convert-longs-to-lobs.sql
--
--  To convert LONG fields to CLOB and LONG RAW fields to BLOB:
--    $ sqlplus -S dbuser/dbpass@dbsid @convert-longs-to-lobs.sql CONVERT
--
-- NOTICE AND DISCLAIMER:
--   Always understand the code you are about to run and test it
--   thoroughly before attempting on a production database. The
--   author of this program is not responsible for any data loss
--   resulting from its use.
--
-- LICENSE:
--   This work is dedicated to the public domain. No rights reserved.
-- 
--   I, the copyright holder of this work, hereby release it into the public
--   domain. This applies worldwide.
--
--   I grant any entity the right to use this work for any purpose, without
--   any conditions, unless such conditions are required by law.
--
--   If you require a fuller legal statement, please refer to the Creative
--   Commons Zero license:
--
--   http://creativecommons.org/publicdomain/zero/1.0/
--

set feedback off;
set define on;
set verify off;
set serveroutput on format wrapped;
set linesize 9999;

-- don't prompt the user for the value of 1 if it's not set
column argv new_value 1 noprint
  select 1 argv from dual where 1=2;

declare
  long_count number;
  operating_mode varchar2(8);
  argv varchar2(2000);
  new_data_type varchar2(20);
  mod_sql varchar2(2000);
  table_count number;
  column_count number;
  index_count number;
begin
  dbms_output.put_line('Sakai LONG-to-LOB field scanner/converter');
  dbms_output.put_line('=============================================='||chr(10));

  argv := '&1';
  long_count := 0;
  operating_mode := 'SCAN';

  if argv = 'CONVERT' then
    operating_mode := 'CONVERT';
  end if;

  if operating_mode = 'SCAN' then
    dbms_output.put_line('Entering SCAN mode.'||chr(10));

    dbms_output.put_line('Scanning for LONG and LONG RAW fields:');
    for t in (select table_name||'.'||column_name as tcolname, data_type from user_tab_columns where data_type in ('LONG','LONG RAW') order by table_name, column_name) loop
      dbms_output.put_line(' - '||t.tcolname||' is of type "'||t.data_type||'"');
      long_count := long_count + 1;
    end loop;
    if long_count = 0 then
      dbms_output.put_line(chr(10)||'No LONG or LONG RAW fields were found in this schema.');
    else
      dbms_output.put_line(chr(10)||'A total of '||to_char(long_count)||' LONG and LONG RAW fields were found.');
      dbms_output.put_line(chr(10)||'Run this script with "CONVERT" as an argument to convert them to LOBs.');
    end if;
  end if;

  if operating_mode = 'CONVERT' then
    dbms_output.put_line('CONVERT mode selected. Data types will be modified!');
    table_count := 0;
    column_count := 0;
    index_count := 0;

    for t in (select distinct table_name from user_tab_columns where data_type in ('LONG','LONG RAW') order by table_name) loop
      dbms_output.put_line(chr(10)||'- Processing '||t.table_name||':');
      table_count := table_count + 1;
      for c in (select column_name, data_type from user_tab_columns where data_type in ('LONG','LONG RAW') and table_name = t.table_name order by column_name) loop
        column_count := column_count + 1;
        if c.data_type = 'LONG' then
          new_data_type := 'CLOB';
        end if;
        if c.data_type = 'LONG RAW' then
          new_data_type := 'BLOB';
        end if;

        dbms_output.put_line(' + Converting column '||c.column_name||' from '||c.data_type||' to '||new_data_type);
        mod_sql := 'alter table '||t.table_name||' modify '||c.column_name||' '||new_data_type;
        dbms_output.put_line('   * Executing SQL: "'||mod_sql||'"');
        execute immediate mod_sql;
      end loop;

      dbms_output.put_line(' + Rebuilding indices for '||t.table_name);
      -- select all indexes from these tables other than LOB indexes which cannot be rebuilt
      for i in (select index_name from user_indexes where table_name = t.table_name and index_type <> 'LOB') loop
        index_count := index_count + 1;
        mod_sql := 'alter index '||i.index_name||' rebuild online';
        dbms_output.put_line('   * Executing SQL: "'||mod_sql||'"');
        execute immediate mod_sql;
      end loop;
    end loop;

    dbms_output.put_line(chr(10)||'==============================================');
    dbms_output.put_line(' Tables converted: '||to_char(table_count));
    dbms_output.put_line('Columns converted: '||to_char(column_count));
    dbms_output.put_line('  Indices rebuilt: '||to_char(index_count));

    dbms_output.put_line(chr(10)||'Coversion complete!');
  end if;
end;
/

exit;