Searching a text in long datatype column

There is a situation that we have created a table with LONG datatype column and we wanted to search a string in that text.

We use LONG datatype columns to store characters of length upto 2 GB. This is also called big version of VARCHAR2.

The long datatype is deprecated in the  newer versions of the Oracle release. But they are still maintained in the old legacy systems. As per Oracle, the use of LONG datatype must be restricted to the below conditions:

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.
• You cannot create an object type with a LONG attribute.
• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
• LONG columns cannot be indexed.
• LONG data cannot be specified in regular expressions.
• A stored function cannot return a LONG value.
• You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
• The UNIQUE operator of a SELECT statement
• The column list of a CREATE CLUSTER statement
• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
• SQL built-in functions, expressions, or conditions
• SELECT lists of queries containing GROUP BY clauses
• SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
• SELECT lists of CREATE TABLE ... AS SELECT statements
• ALTER TABLE ... MOVE statements
• SELECT lists in subqueries in INSERT statements

So, here comes the big question, how to search a string in LONG datatype columns. So here, I have created sample scripts to understand this scenario better:

create table temp (x long, id number);

desc temp;

insert into temp values ('This is log text1',1);

insert into temp values ('This is log text2',2);

insert into temp values ('This is log text3',3);

insert into temp values ('This is text4',4);

select * from temp;

select * from temp where x like '%log%';

So this is the problem we are talking about.

How to resolve this? Convert the LONG column to CLOB and try your search operation there.

Still confused? Check the below code part as well:

create global temporary table temp_global (x clob, id number);

insert into temp_global select to_lob(x),id from temp;

select * from temp_global;

select * from temp_global where x like '%log%'; --Returns 3 rows

select * from temp_global where x not like '%log%'; --Returns 1 row. 

(check the insert script highlighted with green)

So this resolves all the problem with the LONG column. 

Conclusion: Try not creating LONG columns unless they are really needed. Use CLOB datatype instead.

Have you worked on LONG datatype?

Yes

No

Poll Maker

Rebuild unusable Index

The below is the query to rebuild unusable Partitioned and unpartitioned Indexes.

Run the 'Alter index' script when there are unusable indexes.

--Query to rebuild unusable Partitioned and Non Partitioned Indexes

SELECT owner,

       index_name,

       'ALTER index ' || owner || '.' || index_name || ' REBUILD;' stmt

  FROM (SELECT owner,

               table_name,

               index_name,

               NULL partition_name,

               index_type,

               blevel,

               leaf_blocks,

               distinct_keys,

               num_rows

          FROM all_indexes --Non Partitioned tables

         WHERE status NOT IN ('VALID', 'N/A')

        UNION

        SELECT owner,

               table_name,

               i.index_name,

               partition_name,

               index_type,

               ip.blevel,

               ip.leaf_blocks,

               ip.distinct_keys,

               ip.num_rows

          FROM all_indexes i, all_ind_partitions ip --Partitioned Tables

         WHERE     i.owner = ip.index_owner

               AND i.index_name = ip.index_name

               AND ip.status != 'USABLE');

If there are many database actions involved everyday, then we can think of creating a database job for this and running this every day after business hours.