how to find ?? in a view text (definition) in all schemas
saam001Jun 22 2010 — edited Jun 22 2010Hi
DB Version Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
OS RHES 5U2
I would appreciate if someone can tell me how to get a list of all the views in the database that contains ?? in the text/definition of the view.
I tried with both DBA_VIEWS and ALL_VIEWS using following query but it returned error.
SELECT OWNER||'.'||VIEW_NAME
FROM ALL_VIEWS
WHERE TEXT LIKE '%??%'
AND owner not in ('SYS','SYSTEM','SYSMAN','DBSNMP','OLAPSYS','MDSYS','CTXSYS')
ORDER BY OWNER;
WHERE TEXT LIKE '%??%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
SELECT OWNER||'.'||VIEW_NAME
FROM DBA_VIEWS
WHERE TEXT LIKE '%??%'
AND owner not in ('SYS','SYSTEM','SYSMAN','DBSNMP','OLAPSYS','MDSYS','CTXSYS')
ORDER BY OWNER;
WHERE TEXT LIKE '%??%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Thanks