Home » SQL & PL/SQL » SQL & PL/SQL » Find string in view Definitition (Merged N times)
Find string in view Definitition (Merged N times) [message #384372] Tue, 03 February 2009 09:11 Go to next message
gauravpuri2002
Messages: 24
Registered: October 2007
Location: Bangalore
Junior Member
Hi
I want to search a sring 'xyz' in the defination of all views in my database so i am using following query-

select * from dba_views
where to_char(upper(text)) like 'xyz'

but is giving error as inconsistent datatype so please suggest...
Re: find a string in view defination [message #384376 is a reply to message #384372] Tue, 03 February 2009 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How many more times do you plan on spamming forum with same question?
Re: find string in view defination [message #384380 is a reply to message #384372] Tue, 03 February 2009 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How many more times do you plan on spamming forum with same question?
Re: find string in view defination [message #384382 is a reply to message #384372] Tue, 03 February 2009 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
REDUNDANCY is the best way to teach slow learners.
REDUNDANCY is the best way to teach slow learners.
REDUNDANCY is the best way to teach slow learners.
REDUNDANCY is the best way to teach slow learners.
REDUNDANCY is the best way to teach slow learners.
Re: Find string in view Definitition (Merged N times) [message #384389 is a reply to message #384372] Tue, 03 February 2009 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The time I spent to delete the SEVEN too much reposts you made is the time I shoud spend to answer you.

Waiting for your next share, read OraFAQ Forum Guide.

Regards
Michel
Re: Find string in view Definitition (Merged N times) [message #384393 is a reply to message #384372] Tue, 03 February 2009 10:48 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
See this link and look at the last post.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1191679540050

Regards

Raj
Re: Find string in view Definitition (Merged N times) [message #384397 is a reply to message #384372] Tue, 03 February 2009 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to_char(upper(text)) like 'xyz'

There are 3 silly things in so few words:
- to_char on a string
- upper compare to lower case string
- like without pattern-matching character

Regards
Michel
Re: Find string in view Definitition (Merged N times) [message #384405 is a reply to message #384397] Tue, 03 February 2009 12:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
LONG datafields are remnant of old that can be a pain to use.

You will need a function that converts the LONG field into an VARCHAR2 field first.

Have a look at the examples here and here
Re: Find string in view Definitition (Merged N times) [message #384406 is a reply to message #384372] Tue, 03 February 2009 12:10 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you spent more time searching this forum & less time spamming this forum, you'd have a solution be now.

A thread on this forum contains a PL/SQL function which does what you desire.
Previous Topic: DBMS_OUTPUT and UTL_FILE (merged)
Next Topic: insert statement
Goto Forum:
  


Current Time: Sat Dec 10 22:20:51 CST 2016

Total time taken to generate the page: 0.08807 seconds