Querying a long column in Oracle

Sql> CREATE TABLE T_long (ID number, long_col LONG);
The table is created.
Sql> INSERT into T_long VALUES (1, Lpad (1, 32767, ' HELLO world! '));
1 rows have been created.
Sql> COMMIT;
Submit complete.
Sql> SELECT * from T_long WHERE long_col like '%world% '
2;
SELECT * from T_long WHERE long_col like '%world% '
* Error on line 1th:
ORA-00932: Inconsistent data type: number is expected but LONG is obtained
Based on Oracle's recommendations, you should avoid using the long type again. If you should probably use To_lob to rebuild the table, convert the long type in the table to the CLOB or blob type.
This is not the way to think about it, but to discuss how to query for tables that cannot be rebuilt.
If the data in the table does not exceed 32K in length, consider using PL/SQL code similar to the following:
Sql> SET Serverout on
Sql> BEGIN
2 for I in (SELECT * from T_long) LOOP
3 IF INSTR (I.long_col, ' world ') > 0 Then
4 Dbms_output. Put_Line (i.id);
5 END IF;
6 END LOOP;
7 END;
8/
1
The PL/SQL process has completed successfully.
However, the PL/SQL code can handle no more than 32K of data, which exceeds this limit and cannot be processed by PL/SQL.
Fortunately, Oracle's full-text indexing is supported by a long type, and a context index is established, and the query syntax for full-text indexing solves the problem:
Sql> CREATE INDEX ind_t_long_col on T_long (long_col) Indextype is Ctxsys. CONTEXT;
The index has been created.
Sql> SELECT ID from T_long WHERE CONTAINS (Long_col, ' world ') > 0;
Id
----------
1

Querying a long column in Oracle