How to find all views containing to_char Function 1

[mdl2]

Hi I want to know how to find all view in our system that have the function to_char

I tried
select owner,view_name,text
from all_views
where text like '%to_char%'

got inconsistent datatypes text is defined as long

does anyone know of a way for me to get this info

Thanks

 

[SantaMufasa]

<soapbox>

I will be SOOOOO glad when Oracle disinfects LONG columns from all of its data dictionary objects, replacing those columns with CLOBs (which behave just fine).

</soapbox>

Now so you can achieve the results you want, I have built you a user-defined FUNCTION that accesses ALL_VIEWS and returns (in a VARCHAR2(4000) expression) the TEXT of any particular VIEW in ALL_TABLES:

CREATE OR REPLACE FUNCTION view_text (owner_in VARCHAR2, view_in varchar2) RETURN varchar2 IS
    incoming    varchar2(32767);
    return_hold varchar2(4000);
Begin
    select text into incoming from all_views
     where owner = owner_in
       and view_name = view_in;
    return_hold := substr(incoming,1,4000);
    return return_hold;
END;
/

Function created.

To use the VIEW_TEXT function, pass the OWNER and VIEW_NAME of the view whose TEXT you wish to see. Following is a query that uses the VIEW_TEXT function to produce the results for which you are looking:

select view_name from all_views
where upper(view_text(owner,view_name)) like '%TO_CHAR%';

VIEW_NAME
---------------------
ALL_LOB_PARTITIONS
ALL_OBJECTS
DBA_2PC_NEIGHBORS
DBA_DDL_LOCKS
DBA_DML_LOCKS
DBA_IAS_POSTGEN_STMTS
...
USER_REGISTRY
DBA_REGISTRY_HIERARCHY
TEMPLESUBS
YEARS

48 rows selected.

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mdl2]

Thanks a lot -- it worked

Just in case someone else wants to run it -- I had problems in toad 9.1 creating the function with shift f9 -- for some unknown reason it gave me a sql error but when I pressed the execute statement button it worked

Thanks again !!!!!