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;
/