Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Search for value in field across database
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.
Answers
-
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?
-
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.
-
@mathguy : because people want a google-like all-knowing search on their own data... so that ends up in requirements.
-
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..
-
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.
-
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.
-
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
-
Yes that is the tool .. sql developer is more like ssms in my opinion and neither have the search functionality.
-
-
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.
