How to process the LONG type in ORACLE

1. Process in block:

 

However, PL/SQL code can only process data up to 32 KB. If this limit is exceeded, PL/SQL cannot be used for processing.

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/

 

2. query syntax for full-text index:

 

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