• Adi Jaradat

Search All Maximo Tables and All Columns

Updated: Jun 21

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
          ,' AS SIMILAR_ATTRIBUTE
      FROM DUAL
)
,ALL_POTINTIAL_COLUMNS AS (
    SELECT UTC.*
          ,ROWNUM
      FROM USER_TAB_COLUMNS TC
          ,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

Links

https://adijaradat.com/post/constructing-a-calendar-query/

https://docs.oracle.com/cd/B10500_01/appdev.920/a96620/xdb04cre.htm

https://docs.oracle.com/cd/B19306_01/appdev.102/b14288/exprn_xpathpredicates.htm

7 views0 comments

Recent Posts

See All

Constructing a Calendar Query

A well designed query to help you construct a dynamic calendar starting from current system date and adds a configurable number of months.