how to search a text in a schema
I have 100 tables in my schema. I would like to search say word 'hello' in my schema.How can I search it quicly and effectively in my entire schema in all tables. Any sample code, resources, ideas, links highly appreciated. Thanks in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree, the whole credit should go to you. This piece of code should be a HOT could be saved in a place here in EE for the future use.
If its works please spilit the points with the owner of this code (sdstuber).
If its works please spilit the points with the owner of this code (sdstuber).
ASKER
when i did like
In TOAD,Search->Object Search and enter the Search Term.
Click the Schema's, Object Names, Source etc.
says
ORA-00942: table or view does not exist
Instead of searching for the data looks like searching for table name.Any specific options to select to search on data. Please advise
don't worry about the split, I just thought it was funny to have my own code beat me:)
for the TOAD error, you were probably searching through objects you don't have privileges to do SELECT on.
the pl/sql block above should be safe because it uses the ALL_TAB_COLUMNS view which should only be populated with the tables you can query
for the TOAD error, you were probably searching through objects you don't have privileges to do SELECT on.
the pl/sql block above should be safe because it uses the ALL_TAB_COLUMNS view which should only be populated with the tables you can query
let me clarify....don't worry about splitting points for code I posted in another question.
you can give me a split if I posted something else here that is helpful though!
:)
you can give me a split if I posted something else here that is helpful though!
:)
Does this screen shot help you?
I have modified a bit in order to protect our organization settings
Object-Search-TOAD.jpg
I have modified a bit in order to protect our organization settings
Object-Search-TOAD.jpg
Sounds Great SD.
In order to make it work this in TOAD you should be having access to dba data dictionary views. Please ask your dba to grant you "SELECT_CATALOG_ROLE". Grant this role to allow users SELECT privileges on data dictionary views.
If you have a user with DBA role/privileage use that one for the search.
Did you try the code snippet that I've posted? It worked great for me and other people here in ee.
In order to make it work this in TOAD you should be having access to dba data dictionary views. Please ask your dba to grant you "SELECT_CATALOG_ROLE". Grant this role to allow users SELECT privileges on data dictionary views.
If you have a user with DBA role/privileage use that one for the search.
Did you try the code snippet that I've posted? It worked great for me and other people here in ee.
ASKER
thank you very much
glad we could help
I wrote it!
but other people have written similar loops too!