Search All Maximo Tables and All Columns
Updated: Feb 25
There are many way to implement Google-like search facility for any system such as Maximo. The advantage of have Google-like search is the ability to search all column in the database.
Besides the well-known and well-document Open Source solutions, proprietary solutions are also equally capable. However, not all organizations have the need or capacity to introduce a new system to their portfolio given that any system requires some sort of investment.
If you have a capable database that is able to withstand hefty queries you can use the below code to achieve Google-like (not entirely) search but bear in mind that performance will be your bottle nick.
I adapted this query to allow me to search for any value that contains a specific text and produce Select and Update statements. This adaptation is especially interesting when you are faced with situations where you need to apply a data update and you are not sure where this data has been used.
To help this query perform better I have limited the search to all columns which are of the same length or longer than the target column. The narrower the number number of tables and columns the better performance you will get, in addition to, the database over-all performance. Try not to attempt to run this query on exhausted database!
One of the main Oracle-specific features used in this query is “XMLType” datatype because it enables SQL operations on XML content, as well as XML operations on SQL content. You may need to read a bit about “XPath” to completely understand the example.
It is worth to mention that some database permissions are required to execute this query.
WITH PARAMS AS ( SELECT 'MY OLD VALUE' AS OLD_VALUE ,'MY NEW VALUE' AS NEW_VALUE ,'LOCATIONS' AS SIMILAR_OBJECT ,'LOCATION' AS SIMILAR_ATTRIBUTE FROM DUAL ) ,ALL_POTINTIAL_COLUMNS AS ( SELECT UTC.* ,ROWNUM FROM USER_TAB_COLUMNS UTC ,SYS.ALL_OBJECTS ALLOBJ WHERE DATA_TYPE IN ('CHAR', 'VARCHAR2') AND DATA_LENGTH >= ( SELECT LENGTH FROM MAXATTRIBUTE WHERE OBJECTNAME=(SELECT SIMILAR_OBJECT FROM PARAMS) AND ATTRIBUTENAME=(SELECT SIMILAR_ATTRIBUTE FROM PARAMS) ) AND ALLOBJ.OBJECT_NAME=TABLE_NAME AND ALLOBJ.OBJECT_TYPE='TABLE' ) SELECT TABLE_NAME ,COLUMN_NAME ,'SELECT '||COLUMN_NAME||' FROM '||TABLE_NAME ||' WHERE '||COLUMN_NAME||'='''||(SELECT OLD_VALUE FROM PARAMS)||''';' SELECT_QUERY ,'UPDATE '||TABLE_NAME ||' SET ' ||COLUMN_NAME||'='''||(SELECT NEW_VALUE FROM PARAMS)||''' WHERE '||COLUMN_NAME||'='''||(SELECT OLD_VALUE FROM PARAMS)||''';' UPDATE_QUERY FROM ALL_POTINTIAL_COLUMNS WHERE TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM ' || TABLE_NAME ||' WHERE TO_CHAR(' || COLUMN_NAME || ') = '''||(SELECT OLD_VALUE FROM PARAMS)||'''')),'ROWSET/ROW/C')) > 0