Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K 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
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 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
- 473 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
Answers
-
So, it's not a user-requirement but a convenience-requirement for a developer?
-
In a way. I am tasked with developing crystal Reports based on the Oracle data. Finding which tables hold the required data for reports can be very difficult. Being able to search for a value is one way to track which tables a transaction within the erp system records data. I am not an oracle developer but can use basic SQL language.
-
So you are "hacking" vendor app. Not a good idea. Oracle ERP is proprietary product and you simply can't rely on table structure and where what data lives since it can change from one release to another thus breaking your reports. That's why ERP has own reporting.
SY.
-
Solomon. I don't understand why you would call this "hacking". The ERP app in question has limited reporting capabilities provided by crystal reports. No company I have ever seen has purely used builtin ERP reports. Every company connects the data to other data reporting and analysis tools to provide reporting/dashboards etc. to end users. It is my job at an end user site to produce custom reports for the end user company. You are correct in saying that database structures can change but in my experience it is usual that fields are added to tables in updates but rarely moved or taken away.
As stated previously there are tools produced to achieve this in SQL Server so it is unlikely I am the only person who would like this functionality in Oracle.
-
I put "hacking" in quotes. What I mean is assume you found some attribute is stored in table TBL_A column COL1 and you created report using that. Some time later new ERP version is released and your company decides to upgrade. And in that new release that attribute your report needs resides in TBL_B COL5 which breaks your reports. So you will have to verify all attributes your reports are using reside in same place in the database, are stored same way (e.g. it was DATE and now it is TIMESTAMP), etc. each time before ERP is upgraded. And possibly search whole database again trying to figure out where/how these attributes are implemented in that new release.
SY.
-
A better example would be this. Lets say I don't know which table the Scrap Recorded against a Works Order field is stored. I can enter an 'unusual' scrap amount of maybe 34567.765 and then search the database to see where that value is stored. That would give me the field name and table name .. maybe field: sc_record in table inv_trans for example.
-
As stated previously there are tools produced to achieve this in SQL Server so it is unlikely I am the only person who would like this functionality in Oracle.
And I have posted a link that shows sample code (or a "pipeline tool") that does exactly this in my initial response.
Try it.