SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Oracle Search Utility

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Oracle Search Utility
Author Message
DJ Munning



Joined: 16 Jan 2000
Posts: 3

Post Oracle Search Utility Reply with quote

Does anyone know a good Oracle search utility or has anyone written a search script they would be willing to share?

Wed Feb 23, 2000 2:57 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Re: Oracle Search Utility Reply with quote

You can use dbFind included with dbMonitor (part of the dbTools package). It can search virtually anything in your database.

See Tools / Object Finder menu

: Does anyone know a good Oracle search utility or has anyone written a search
: script they would be willing to share?

Wed Feb 23, 2000 3:23 pm View user's profile Send private message
DJ Munning



Joined: 16 Jan 2000
Posts: 3

Post Re: Oracle Search Utility Reply with quote

: You can use dbFind included with dbMonitor (part of the dbTools package). It
: can search virtually anything in your database.

: See Tools / Object Finder menu

I looked in the tools but found that the tools only search for objects. I need to search for values. For instance, if I want to see all the tables and columns where %DLLS% occurs, a report is generated showing that information. Make sense?

Thu Feb 24, 2000 3:37 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Re: Oracle Search Utility Reply with quote

It does search for objects that have %your criteria% in their names, in the names of their columns, in their comments, code, constraints, indexes, etc. You specify "what" and "where" your want to search. If %your criteria% is found in the column name then the output report shows such object in format "owner.table_name.column_name".

What do you mean by values? A data search in the table data?

: I looked in the tools but found that the tools only search for objects. I
: need to search for values. For instance, if I want to see all the tables
: and columns where %DLLS% occurs, a report is generated showing that
: information. Make sense?

Thu Feb 24, 2000 9:08 am View user's profile Send private message
DJ Munning



Joined: 16 Jan 2000
Posts: 3

Post Re: Oracle Search Utility Reply with quote

Right, but what I am looking for is slight different.

Lets say for instance you have 2 tables, emp and dept. The tables are setup as follows ...

EMP
Name Favorite Color Job
DJ Blue Not Much
Sam Red Same Thing

DEPT
Name Wall Color
Dev BlueGreen
Eng Purple

So, I being the CEO get hit by a blue car and am not happy with the color. I decide that Blue must be stricken from our database. I want a report that will search the entire database for any value %BLUE% and return the table_name, and column_name in something like this:

TABLE_NAME COLUMN_NAME VALUE
emp favorite_color Blue
dept wall_color BlueGreen

See, it is like a full table search of each column and each row in a database for a specific value to return the table_names and column_names for each instance where that record occurs.

DJ

: It does search for objects that have %your criteria% in their names, in the
: names of their columns, in their comments, code, constraints, indexes,
: etc. You specify "what" and "where" your want to
: search. If %your criteria% is found in the column name then the output
: report shows such object in format
: "owner.table_name.column_name".

: What do you mean by values? A data search in the table data?

Thu Feb 24, 2000 3:16 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Re: Oracle Search Utility Reply with quote

Send this as an enhacment request to monitor@softtreetech.com. Such feature may be added to the dbFind in a near future.

If you are dealing with a small number of tables you can build a script like

define search_string 'Blue';

select 'emp' table_name, 'name' column_name, name data_value
from emp
where name like %&search_string%
union all
select 'emp' table_name, 'favorite_color' column_name, favorite_color data_value
from emp
where favorite_color like %&search_string%
union all
select 'emp' table_name, 'job' column_name, job data_value
from emp
where job like %&search_string%
union all
select 'dept' table_name, 'name' column_name, name data_value
from dept
where name like %&search_string%
union all
select 'dept' table_name, 'wall_color' column_name, wall_color data_value
from emp
where wall_color like %&search_string% ;

If you run such script using DBA Notepad you would get the output like

Table Name Column Name Data Value
emp favorite_color Blue
dept wall_color BlueGreen

You will need to declare a substitution variable called for example search_string.
So that to change the criteria for a new search all you need to do before running the script is to change the first line. For example to serach for Grean change it to "define search_string 'Green';"
: Right, but what I am looking for is slight different.

: Lets say for instance you have 2 tables, emp and dept. The tables are setup
: as follows ...

: EMP
: Name Favorite Color Job
: DJ Blue Not Much
: Sam Red Same Thing

: DEPT
: Name Wall Color
: Dev BlueGreen
: Eng Purple

: So, I being the CEO get hit by a blue car and am not happy with the color. I
: decide that Blue must be stricken from our database. I want a report that
: will search the entire database for any value %BLUE% and return the
: table_name, and column_name in something like this: TABLE_NAME COLUMN_NAME
: VALUE
: emp favorite_color Blue
: dept wall_color BlueGreen

: See, it is like a full table search of each column and each row in a database
: for a specific value to return the table_names and column_names for each
: instance where that record occurs.

: DJ

Thu Feb 24, 2000 4:07 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.