Filter "text" column on all_views

Question

Is there any way I could filter the text column on oracle's all_views table?

For example:

SELECT * 
  FROM ALL_VIEWS 
  WHERE UPPER(TEXT) LIKE '%FOO%';

Exception:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Edit:

DESC ALL_VIEWS
Name             Null     Type           
---------------- -------- -------------- 
OWNER            NOT NULL VARCHAR2(30)   
VIEW_NAME        NOT NULL VARCHAR2(30)   
TEXT_LENGTH               NUMBER         
TEXT                      LONG()         
TYPE_TEXT_LENGTH          NUMBER         
TYPE_TEXT                 VARCHAR2(4000) 
OID_TEXT_LENGTH           NUMBER         
OID_TEXT                  VARCHAR2(4000) 
VIEW_TYPE_OWNER           VARCHAR2(30)   
VIEW_TYPE                 VARCHAR2(30)   
SUPERVIEW_NAME            VARCHAR2(30)

what you are comparing with numbers then ?? do you want to find XX from table who have 1 in their `TEXT`?? To work `LIKE` field has to be varchar() type... — Fahim Parkar, Feb 10 '12 at 14:04
could you please exactly let me know what you want out of this table?? like i want to find the employee whom `SUPERVIEW_NAME` contains `abc` — Fahim Parkar, Feb 10 '12 at 14:05
**all_views** is where oracle saves this kind of database objects. I want to find all views on my database that refers to a certain table. — Vitor Freitas, Feb 10 '12 at 14:09
the problem is that `TEXT` is LONG and LONG can't be used in `LIKE`... — Fahim Parkar, Feb 10 '12 at 14:10
I know, and thats my problem... the text column the select statement of the view (although its a LONG column). The sql were just a example of what I want... — Vitor Freitas, Feb 10 '12 at 14:15
so what is the field in `ALL_VIEWS` that refer to the certain table?? — Fahim Parkar, Feb 10 '12 at 14:27
I think the question title needs to change to match the solution - maybe it should be "Find base tables in a view" ..? — Kent Pawar, May 13 '14 at 19:51

score 20 · Accepted Answer · answered Feb 10 '12 at 15:26

You can't convert to a clob on the fly via a select statement unfortunately. to_lob function works with INSERT statements, but that would mean you'd need to setup a separate table and do inserts into using to_lob.

You can do assignment conversions to varchar in pl/sql, and most of the time you'll find that the text_length in all_views is < 32767, so this will cover "most" cases, although its not a nice as just selecting:

declare

  l_search varchar2(1000) := 'union';
  l_char varchar2(32767);

begin
  for rec in (select * from all_views where text_length < 32767)
  loop
    l_char := rec.text;
    if (instr(l_char, l_search) > 0) then
      dbms_output.put_line('Match found for ' || rec.owner || '.' || rec.view_name);
    end if;
  end loop;

end;

Here I'm searching the text field for the string 'union'.

Hope that helps.

score 13 · Answer 2 · answered Feb 10 '12 at 14:28

You can't manipulate LONG columns easily in SQL unfortunately.

For your present problem, as a workaround, you could use the *_DEPENDENCIES views to find all views dependent upon a table:

SELECT * 
  FROM all_dependencies 
 WHERE type = 'VIEW' 
   AND referenced_owner = 'TABLE_OWNER'
   AND referenced_name = 'YOUR_TABLE';

score 4 · Answer 3 · edited Jul 20 '18 at 17:44

4

Search on TEXT_VC instead of TEXT

SELECT * 
 FROM ALL_VIEWS 
 WHERE UPPER(TEXT_VC) LIKE '%FOO%';

edited Jul 20 '18 at 17:44

EstevaoLuis

2,422
7
33
40

answered Jul 20 '18 at 15:30

user2785110

75
4

Igor Krupitsky · Answer 4 · 2017-04-24T15:52:19.713

0

On Oracle Developer run and export to Excel

SELECT view_name, text from all_views where owner = 'MyDb'

Import the Excel file to SQL Server (to [ALL_VIEWS] table)

SELECT [VIEW_NAME], [TEXT] FROM [MyDb].[dbo].[ALL_VIEWS] WHERE [TEXT] LIKE '%FOO%'

edited Apr 24 '17 at 15:52

answered Apr 24 '17 at 15:29

Igor Krupitsky

787
6
9

score 0 · Answer 5 · edited Oct 29 '18 at 19:53

If you are on a pre 12c (text_vc only exists in 12.1+) and you can sufficiently scan your views through the first 4000 characters the following query will work.

with parsed_all_views as (
  select owner, view_name, text_length, TEXT as VARCHAR_TEXT
  from xmltable( '/ROWSET/ROW' passing dbms_xmlgen.getXMLType(
    'select TEXT, owner, view_name, text_length  '
    ||' from all_views WHERE owner = ''**YOUR_SCHEMA_NAME**'' '
  ) columns  TEXT varchar2(4000), owner varchar2(32), view_name  
  varchar2(32), text_length  varchar2(32)) 
)

select * from parsed_all_views where upper(VARCHAR_TEXT) like '%DISTINCT%'

Filter "text" column on all_views

5 Answers5