Search for value in field across database

User_7WIPY

Version 12c: simple really … how do I search for a value across all tables within a database?

example:

database name: Dallas

Multiple tables

searching for value : 956377

Search maybe for text or numeric value.

value may appear in multiple tables and multiple fields.

mathguy

Not knowing what values you may find in the "phone number" in the "customers" table makes sense. That's why we have queries.

Not knowing what table and what column may hold a specific value makes no sense. You don't know if the value you are searching for is a credit card number, a phone number or an employee id? What sense does that make?

Why do you need to look for a given value in all tables and all columns? Do you know what the value is supposed to represent?

This is my long-winded way of saying, "before I will make an effort to try to help you, please convince me that this problem is reasonable." How do you end up having to solve such a problem?

Billy Verreynne

An example I recently posted is at https://community.oracle.com/tech/developers/discussion/comment/16802217#Comment_16802217

It uses a pipeline on dynamic DBMS_SQL cursors to search the tables.

[Deleted User]

@mathguy : because people want a google-like all-knowing search on their own data... so that ends up in requirements.

Alex Nuijten

That's a requirement that you hear a lot, but doesn't make sense.... (to me)

Even with Google you search for something specific. You don't search for "a" or "1" just to see where it shows up in the database. You search for an "invoice number" or a "customer name" to get your work done. Users don't go browsing through the database just to pass the time.

Knowing how the application will be used and what your users search for is more beneficial. We use Oracle Text to create a Text index on multiple tables, so that the users can search for invoices, orders, names and so on. Plus you get the benefit of alternate spelling searches (search for "Moeller" will also find "Möller" - it's a German application) and it's really fast.

just my 2 ct..

User_7WIPY

Thanks for the responses. The oracle database is an ERP system. There is no documentation on the tables. I might be looking to see which table holds certain stock transactions for example. I can create a transaction with an unusual value on the erp system then I would like to see where that value appears in the database. When I worked with SQL server I had a utility called apexsql search that achieved exactly what I needed.

BEDE

If the database is not documented, I can give you some advice. Look for the cosntraints in all_constraints and all_cons_columns to find the primary keys and foreign keys. Foreign keys will show the links between tables. If there are no foreign keys (I've seen such cases) then that's pretty bad. Also look into all_tables for table names to see what the table names suggest - that is if tables were named so as to indicate their purpose. Look also into all_tab_columns. You may imagine some relations between tables even if they are not represented in the database as foreign keys. Try to check the relations you may think of by using selects with outer joins.

That's something I did in such situations as you describe.

Alex Nuijten

Is this the tool you're referring to? https://www.quest.com/apexsql/

Looks a bit like SQL Developer: https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html

User_7WIPY

Yes that is the tool .. sql developer is more like ssms in my opinion and neither have the search functionality.

User_7WIPY

Solomon Yakobson

https://community.oracle.com/tech/developers/discussion/comment/16808999#Comment_16808999

Google deals with non-structured data. Relational database data is structured and we know (well we are supposed to know) where what data resides.

SY.