Toad World® Forums

How about another search option?


#1

Hi developers,

from time to time I do a search in USER_SOURCE to look for the occurrence of a tablename or another word that I have written years ago.
I query the db like this:

select*from USER_SOURCE where instr(text,‘HATSCHI_BRATSCHI’) > 0;

HATSCHI_BRATSCHI is the expression I am looking for. I would like to have such a search in more advanced form (e.g. not case sensitive, include DBA_SOURCE …) in the search menue of the T.O.A.D., for instance a “Find in source” option.

Btw. in my recent install of TOAD 10.1 the schema browser complains constantly about datatype is not supported and refuses working.
Has anybody noticed a similar behaviour too or am I doing something wrong?
ORACLE is still 9iR2

best regards
Hans Hauer, Vienna


#2

#3

Have you used the Object Search?

It searches pl/sql source code, and has ability to do case sensitive or
not…


#4

NOT CASE SENSITIVE
Use UPPER function on column to search
select*from USER_SOURCE where instr(upper(text),‘HATSCHI_BRATSCHI’) > 0;

INCLUDE DBA_SOURCE
First of all if you search DBA_SOURCE that includes USER_SOURCE so just use DBA_SOURCE


#5

Oh , how could I have overlooked this “search source” option.
Thank you Jeff and Wim.

Thank you Wim for the “upper” recommendation.
I have to confess that I do not use the object search very much because of 2 annoying circumstances:

  1. There is no “select ALL” / “select NONE” / “restrict to favorites” option for the schemas box, if you have 250 schemas and you do not know where your expression could be, you have to click at least 250 times. An option ALL/NONE for the object classes you want to search also would be useful too in my opinion (“search object names” box).

  2. Performance:

select * from dba_source where instr(lower(text),‘b_erbu’)>0

comes back after approximatly 2.5s, the same search done via object search I did cancel after 7 minutes. “Processing results…” message in the statusline did not disappear btw.

Anyway it does not matter anyhow. Because I am too lazy to write one simple line of SQL the TOAD needs not to be enhanced again.

sincerely
Hans


#6
  1. There is no “select ALL” / “select NONE” /

I guess you did not try Right-Clicking in the “Schema to Search” or other boxes?
You should see a right click menu with those options. Remember in Toad if you
don’t see something try right-clicking.

Ed
[TeamT]


#7

#8

Afternoon from snowy Leeds,

  1. There is no “select ALL” / “select NONE” /

Right click on the lust of schemas box, select all, select none, invert
selection, select all except sys/system …
Toad has lots of (hidden) options on the right click menus.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#9

If you use the Session > Oracle Users List filters, it will give you the same
effect.

Assuming you don’t need to browse those other schemas in the Schema
Browser, you should be all set.


#10

lust of schemas box

Norm - what’s on your mind ???

:slight_smile:


#11

Hi Bert,

lust of schemas box Norm - what’s on your mind ??? :slight_smile:

Ah yes, I was hoping no-one would notice that slight slip up. My
spelling chequer certainly didn’t!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#12

Thanks for pointing out this utility, which I hadn’t used before.

However, when I set up search criteria and press the Search button, I get “ORA-00942: table or view does not exist”.
Any ideas what’s missing ? I’m using Toad 9.7.2.5
Cheers
Jason


#13

Thanks for pointing out this utility, which I hadn’t used before.

However, when I set up search criteria and press the Search button, I get “ORA-00942: table or view does not exist”.
Any ideas what’s missing ? I’m using Toad 9.7.2.5
Cheers
Jason


#14

ORA-99555 database administrator too old ;-(
Thanks everybody for remembering me.

Due to the spool SQL feature in TOAD I was able to isolate the slow object search query:

Select DISTINCT allsrc.owner, allsrc.TYPE result_type,
‘Source Search’ object_type, allsrc.NAME object_name
FROM SYS.DBA_SOURCE allsrc, SYS.DBA_OBJECTS allo
WHERE INSTR (UPPER(allsrc.text), ‘SOME STRING’) <> 0
AND allo.owner = allsrc.owner
AND allo.object_name = allsrc.NAME
AND allo.object_type = allsrc.TYPE
AND allsrc.TYPE IN (‘PACKAGE BODY’)
AND allo.status = ‘VALID’;

I was searching for only valid code.

Without the restriction to VALID (or invalid) code the query performs two hundred(!) times better with our DB. So this option should be set to “both” if you want to do a fast PL/SQL code search.
We have around 20k tables and approximatly 2 millions lines of source.
Just my 2 cents for the files…

best regards from the city of Dr. Freud

Hans


#15

Before you click the search button Select Database–>Spool SQL - “Spool SQL to
Screen” from the main menu.

This will show you what statements are being executed and which one is failing.
Post the one that is failing.

Sounds like you don’t have permissions on one of the tables the search is trying
to use.

Ed
[TeamT]


#16

Ed - Thanks, that’s a great tip!
When I got the SQL up, I just ran it in the editor, which revealed I didn’t have access to SYS.ALL_JOBS (wierd, as I DO have access to SYS.DBA_JOBS). This is easily worked round by unticking 'Jobs’in the ‘Search object names’ list.
Thanks for your help
Jason


#17

image001.png


#18

Hans,

You can right click in the schemas box or the object type box to get options
like ‘select all’, ‘select none’, etc.

-John


#19

My toad suddenly seems to cannot respond to the key of Backspace on my key
board, lap tap (notebook).

Thanks & With best wishes

Peng Wei
image001.png


#20

My toad suddenly seems to cannot respond to the key of Backspace on my key
board, lap tap (notebook).

Thanks & With best wishes

Peng Wei
image001.jpeg