Just what is a filter? Well, the American Heritage Dictionary tells us:
fil·ter (fĭl'tər)  n. Computer Science A program or routine that blocks access to data that meet a particular criterion: a Web filter that screens out vulgar sites.

In Toad’s case, a filter is a mechanism that allows us to limit or define more explicitly the information we want to view. Toad asks Oracle for information and then displays it back to the user in a manner that is more appealing than SQL*Plus.  This is the beauty of Toad in a nutshell.

Toad has MANY filters available for making Oracle easier to work with.  Here are my favorites, but if you see one you think deserves a shout-out, make sure to leave a comment at the end of the blog so everyone can learn from each other.

Schema Browser List Filters

There are at LEAST five filters in the Schema Browser alone.  Here’s what each one allows us to do:

1.       Filter objects by Project
2.       ‘QuickFilter’ the object list
3.       Advanced filter for the object list
4.       Add a SQL based filter for the data
5.       Use ‘Excel’ type filters for the data.

Filter objects by Project
 
Our funnel has to be what looks like the ‘book’ image we see in our Project Manager window button graphic.  This is not an accident as it allows the user to choose any project, or sub-project, to act as the filter for the objects that appear in the Schema Browser.  If I have a project called ‘Test’ that has the test objects defined, only the objects defined in that project will show up in the Schema Browser. I know many database administrators who use projects to help organize their regularly scheduled tasks.  A DBA who had to manage a development instance that consisted of many thousands of objects for different projects could use this to easily view project specific objects instead of having to pick and choose between all of them.  You can easily switch from one project to another and have your Schema Browser automatically updated to only see the objects of interest.

‘QuickFilter’ the object list
 
We can also just quickly type in some pattern matching filters for the list of objects already retrieved by the Schema Browser.  The splat, or “*”, in the edit box means, show everything.  This window is powerful because it filters objects that have already been retrieved from the database, e.g. client-side filtering.  I could use this in conjunction with the project filter.  I could say, only show me objects from project ‘ABC’, but also further filter that list by only showing me objects that start with a character between A and C, or “[a-c]*”.  If you have a slow database and you are tired of using the advanced filter, use the ‘QuickFilter’ as you won’t have to wait for the database to respond.  This is also nice feature as Toad will remember your last 25 ‘QuickFilters.’

Advanced filter for the object list

This is by far the most powerful filter in Toad.  This window allows the user to tell Toad EXACTLY which objects to bring back on the LHS of the SB.  The options available on the filter dialog will vary depending on the type of object being filtered.  In this case, it is for the tables.  I can tell Toad to only show me tables with particularly named columns, tables of a certain type, or to order them by their tablespace versus their name.  Let’s take a quick look at two things usually overlooked on this screen:


This check box tells Toad to read the options you’ve defined in the dialog, but before sending the SQL query to Toad, it will popup the SQL in an edit box that you can use to change the SQL.  So for the filter defined above, Toad will show you this:

See how Toad finds our objects, or change the SQL to fit your needs!

This allows bypassing what SQL statement the genius Toad developer has defined for this TAB in the Schema Browser and permits the user to write their own.  You are of course on your own if you decide to go this route J

This checkbox tells Toad to bring back EVERY table in the database, regardless of Schema.  Rather than showing us less information, this filter object allows to remove any restriction from the list of objects in the Schema Browser.  This is a fairly frequent question – “How can I get a list of all tables in my database?”  This is probably the easiest way.

Hey, always show me all tables, regardless of the schema selected.

We’ve just gone through three of our filters, and that’s just for the Schema Browser!  We’ll pick up where we left off in next week’s blog. Until then, here’s a parting tip to help prevent you from tripping over a Toad filter:

If you think you’ve accidentally dropped your tables or table data, don’t despair, you may have just forgotten to clear a filter.  If you see Red, your data is being filtered.

References

American Psychological Association (APA):
filter. (n.d.). The American Heritage® Dictionary of the English Language, Fourth Edition. Retrieved July 11, 2007, from Dictionary.com website: http://dictionary.reference.com/browse/filter