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; |
Posts
