Search for value in field across database

Alex Nuijten

So, it's not a user-requirement but a convenience-requirement for a developer?

User_7WIPY

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.

Solomon Yakobson

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.

User_7WIPY

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.

Solomon Yakobson

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.

User_7WIPY

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.

Billy Verreynne

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.