Oracle SQL - Working with long columns (long datatype)

The LONG and LONG RAW datatypes have been deprecated in favour of LOBs for many Oracle versions, yet they still exist in the data dictionary and legacy systems.The Oracle documentation describes the LONG datatype's main restrictions as follows:

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

Despite the size of this list, we'll find that most of the time we are blocked by the two restrictions highlighted above.
--------------------

Issue simulation:
SELECT * FROM MYTABLE WHERE UPPER(MY_LONG_COLUMN) LIKE UPPER('%my_search_word%');

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Advice:
1) Convert the column to a CLOB

SQL> alter table MYTABLE modify (MY_LONG_COLUMN CLOB) ;

2) Write some PL/SQL to do your searching (limited upto 32K)

SQL> declare
    cursor text is select text from all_views where rownum<100;
    v varchar2(4000);
begin
    for c in text loop
        v:=c.text;
        if ( v like '%ZZ%') then
            dbms_output.put_line( 'text is '||substr(v,1,50));
        end if;
    end loop;
end;