Searching a Long Field

As you've probably found out, the Long field type (soon to be expired) is hard to work with. You can't concatenate, use 'Like' or a host of other things. See the error posted below when I try to find which views are based on the EMP table:

SQL> Set Linesize 2000 Pagesize 60
SQL> Column Text Format A100 Word_Wrapped
SQL>
SQL> Select View_Name,
2         Text
3    From User_Views
4   Where Upper(Text) Like '%EMP%';
Where Upper(Text) Like '%EMP%'
         *
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

To allow you to get to this data, create a table with all the fields you need, but change the Long type to an LOB field type.

Create Table User_Views_Lob (
                         View_Name Varchar2(30),
                         Text      Clob
                        );

Insert the records from the original table to the newly created table.

Insert Into User_Views_Lob
Select View_Name,
      To_Lob(Text)
 From User_Views;

You can now go after the data however you wish. If you want to print the entire text though, you'll have to set LONG to the length of the largest clob.

Set Long 15000
Select View_Name,
       Text
  From User_Views_Lob
 Where Upper(Text) Like '%EMP%';

VIEW_NAME                      TEXT
------------------------------ -------------------------------
TOTAL_SAL                      Select Sum(Sal) Sum_Sal, Deptno
                            From Emp
                            Group By Deptno

DEPTRANK                       Select DeptNo, Count(*) Counter
                            From Emp
                            Group By DeptNo

Of course, you'll want to clean up at the end.

Drop Table User_Views_Lob;

---

Additionally, you can get to this data by storing it in a Global Temporary Table as shown:
Create Global Temporary Table GT_Cks On Commit Preserve Rows As -------------------------------------------------- Select Table_Name, To_Lob(Search_Condition) As Chk From User_Constraints Where Constraint_Type = 'C'; Select * From GT_Cks Where Chk like '%IS NOT NULL%'; Truncate Table GT_Cks; Drop Table GT_Cks;