Tip: Replacing Enterprise Application Search

  • 10 December 2020
  • 2 replies
  • 895 views

Userlevel 7
Badge +18

With Enterprise Application Search dropped from Aurena, I came up with a way to crudely replicate this feature using just straight relational Oracle, with no add-ins.

This scans every leftmost column of every index, using both primary and secondary indexes. It only returns results where the table has a _PK index associated with it. I excluded ROWKEY to speed it up a bit, but it’s still somewhat slow.

As well as scanning common indexes, it also scans any UPPER() function-based indexes you might have. It’s otherwise case-sensitive.

The system load on a scan this big is fairly risky, so it only does an exact match. Users typically don’t understand why HelloWorld% is so much faster than %HelloWorld, and I didn’t want to spend time parsing the expression for safety.

I recommend using this for specific searches. Don’t use an internal company ID, for example.

 

You might implement this with a SQL Quick Report like this:

SELECT *
FROM TABLE(c_enterprise_search_api.get_search_results('&SEARCH_STRING'));

 

Here’s the code:

CREATE OR REPLACE PACKAGE c_enterprise_search_api IS
/*
module_ CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'CEnterpriseSearch';
*/

TYPE search_result_typ_ IS TABLE OF VARCHAR2(4000);

FUNCTION get_search_results(
search_string_ IN VARCHAR2) RETURN search_result_typ_ PIPELINED;

FUNCTION get_business_object_from_rowid(
objid_ IN ROWID) RETURN VARCHAR2;

PROCEDURE init;

END c_enterprise_search_api;
/

CREATE OR REPLACE PACKAGE BODY c_enterprise_search_api IS

FUNCTION get_search_results(
search_string_ IN VARCHAR2) RETURN search_result_typ_ PIPELINED
IS
piped_result_ VARCHAR2(32767);
TYPE search_cursor_typ_ IS REF CURSOR;
search_cursor_ search_cursor_typ_;
sql_statement_ VARCHAR2(32767);
search_result_rowid_ VARCHAR2(32767); -- ROWID isn't as safe, since
BEGIN
FOR index_rec_ IN (
SELECT DISTINCT uic.table_name,
'"' || uic.column_name || '"' AS column_expression
FROM user_ind_columns uic
JOIN user_ind_columns uic_pk
ON uic_pk.table_name = uic.table_name
AND uic_pk.index_name LIKE '%\_PK' ESCAPE '\'
AND uic_pk.table_name LIKE '%\_TAB' ESCAPE '\'
AND uic_pk.table_name = REPLACE(uic_pk.index_name, '_PK', '_TAB')
JOIN user_tab_cols utc
ON utc.table_name = uic.table_name
AND utc.column_name = uic.column_name
WHERE uic.column_position = 1
AND utc.data_type = 'VARCHAR2'
AND utc.column_name != 'ROWKEY' -- performance
) LOOP
sql_statement_ :=
'SELECT rowid FROM "' || index_rec_.table_name || '" '
|| 'WHERE ' || index_rec_.column_expression || ' = :search_string';
OPEN search_cursor_ FOR sql_statement_ USING search_string_;
LOOP
FETCH search_cursor_ INTO search_result_rowid_;
EXIT WHEN search_cursor_%NOTFOUND;
piped_result_ :=
'table_name = '
|| index_rec_.table_name
|| ', primary key = '
|| get_business_object_from_rowid(search_result_rowid_);
PIPE ROW (piped_result_);
END LOOP;
END LOOP;

FOR index_rec_ IN (
SELECT table_name,
column_expression
FROM user_ind_expressions
WHERE column_position = 1
) LOOP
-- This code duplication is unavoidable unless you're more clever than me.
-- COLUMN_EXPRESSION is a LONG, so it can't be UNIONED with COLUMN_NAME.
-- COLUMN_EXPRESSION is a LONG, so it can't be filtered in SQL.
-- PIPE can't be called from a child procedure.
IF UPPER(index_rec_.column_expression) LIKE 'UPPER(%' THEN
sql_statement_ :=
'SELECT rowid FROM "' || index_rec_.table_name || '" '
|| 'WHERE ' || index_rec_.column_expression || ' = :search_string';
OPEN search_cursor_ FOR sql_statement_ USING search_string_;
LOOP
FETCH search_cursor_ INTO search_result_rowid_;
EXIT WHEN search_cursor_%NOTFOUND;
piped_result_ :=
'table_name = '
|| index_rec_.table_name
|| ', primary key = '
|| get_business_object_from_rowid(search_result_rowid_);
PIPE ROW (piped_result_);
END LOOP;
END IF;
END LOOP;
END get_search_results;


FUNCTION get_business_object_from_rowid(
objid_ IN ROWID) RETURN VARCHAR2
IS
table_name_ VARCHAR2(30);
view_name_ VARCHAR2(30);
index_name_ VARCHAR2(30);
key_value_ VARCHAR2(32767);
sql_ VARCHAR2(32767);
index_count_ NUMBER;

TYPE cur_typ_ IS REF CURSOR;
cur_ cur_typ_;
TYPE rec_typ_ IS RECORD(
key_value VARCHAR2(4000));
rec_ rec_typ_;

BEGIN
SELECT object_name
INTO table_name_
FROM user_objects
WHERE object_id = dbms_rowid.rowid_object(objid_);
IF table_name_ NOT LIKE '%\_TAB' ESCAPE '\' THEN
RETURN 'ROWID=' || objid_ || '^';
END IF;
view_name_ := REGEXP_REPLACE(table_name_, '_TAB$', '');
index_name_ := view_name_ || '_PK';
SELECT COUNT(*)
INTO index_count_
FROM user_indexes
WHERE index_name = index_name_;
IF index_count_ = 0 THEN
RETURN 'ROWID=' || objid_ || '^';
END IF;
FOR col_rec_ IN (
SELECT ic.column_name,
DECODE(tc.data_type,
'VARCHAR2', '"' || ic.column_name || '"',
'CHAR', '"' || ic.column_name || '"',
'NUMBER', 'TO_CHAR("' || ic.column_name || '")',
'DATE', 'TO_CHAR("' || ic.column_name || '", ''YYYY-MM-DD HH24:MI:SS'')',
'RAW', 'RAWTOHEX("' || ic.column_name || '")',
'''BAD COLUMN''') AS expression
FROM user_ind_columns ic
INNER JOIN user_tab_cols tc
ON tc.table_name = ic.table_name
AND tc.column_name = ic.column_name
WHERE ic.index_name = index_name_
)
LOOP
sql_ :=
'SELECT ' || col_rec_.expression || ' AS key_value'
|| ' FROM ' || table_name_
|| ' WHERE rowid = :objid_';
BEGIN
OPEN cur_ FOR sql_ USING IN objid_;
FETCH cur_ INTO rec_;
CLOSE cur_;
EXCEPTION
WHEN OTHERS THEN
CLOSE cur_;
RAISE;
END;
key_value_ := key_value_
|| col_rec_.column_name || '=' || rec_.key_value || '^';
END LOOP;
RETURN key_value_;
END get_business_object_from_rowid;

PROCEDURE init IS BEGIN NULL; END init;

END c_enterprise_search_api;
/

 


2 replies

Userlevel 3
Badge +7

Hello Kevin!

Your efforts here were pointed out to me by one of the consultants that I support, so I took your custom package and deployed it into one of our internal Apps10 UPD10 environments, and then created a Quick Report using your example Get_Search_Results function call.

I have some questions as to how this package works based on my reading of the code, but in a more general sense - can you give an example scenario where you are searching for a phrase and would normally use EAS on the IEE side, and then show how your solution would work on the Aurena side?  So far, I’ve been unable to get the QR to return any search results, but I may not be understanding how to utilize this correctly.

Thank you!
Rob Simon

Userlevel 7
Badge +18

Hello Kevin!

Your efforts here were pointed out to me by one of the consultants that I support, so I took your custom package and deployed it into one of our internal Apps10 UPD10 environments, and then created a Quick Report using your example Get_Search_Results function call.

I have some questions as to how this package works based on my reading of the code, but in a more general sense - can you give an example scenario where you are searching for a phrase and would normally use EAS on the IEE side, and then show how your solution would work on the Aurena side?  So far, I’ve been unable to get the QR to return any search results, but I may not be understanding how to utilize this correctly.

Thank you!
Rob Simon

It looks at all the indexes that end in _PK associated with tables than end in _TAB, and it looks at the leftmost columns of those indexes to do the search.

I wrote this to be a more limited version of something I use for writing data repairs. In that bigger version, I remove the requirement that the column is indexed so it’s guaranteed to return everything. A single search on that code can take a whole day of machine time but is worth the trouble when system integrity is at stake.

The fact that it’s limited to the leftmost column is the most restrictive part of this code. Often, the most interesting column is not the leftmost column on the index. For something like a customer order number or a sales part, this works, but for something like a voucher, the leftmost column is the company, which is useless without being able to search by a voucher number.

Reply