Re: Find database objects in Oracle

[Quoted] On Thu, 05 Jan 2006 06:50:07 -0800, ross.oneill wrote:

>
> Hi I want to find a field that holds the doctors name in a database. I
> want to do a wildcard search by field name like %doc% or %phy%. This
> is something I would do in SQL Server but I am not familiar with
> oracle. Can you search by field name in Oracle?
>

An Oracle schema is similar to a SS database. (Oracle came first - SS changed the meaning of database. <g>). A schema is a collection of tables with columns (and other database objects).

I am not aware of anything called a field in Oracle databases. I suspect you mean a column, based on many SS applications with user interfaces that have fields to map back to columns.

My suspicion is you want to select the rows from a table based on one or more columns containing the strings 'doc' or 'phy'. That could be accomplished by

  SELECT {list of columns and other column expressions}     FROM table
   WHERE (column LIKE '%doc%' OR column LIKE '%phy%')      AND {other selection criteria}     

Unfortunately, Oracle does not automagically ignore case, so you will likely need to do things like

   WHERE (UPPER(column) LIKE '%DOC%' OR UPPER...

which can have preformance issue if you have a lot of these or the data set is significant (millions of rows)

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting [replies] guarantees I won't respond. ***