Thursday, July 26, 2012

PLS-00201: identifier 'CONTENT_SEARCH.SEARCH_WITH_RESULTSETINTERFACE' must be declared

Recently, we configured our WebCenterContent (UCM) 11g system to use OracleText as the Search Indexing Engine.

After the Collection was successfully rebuilt, the search was throwing the below error. No results were returned, and always got the error "Unable to return results" on the screen.

*******************************************************************************
>systemdatabase/6    07.20 15:05:05.987    IdcServer-39527    Prepared statement is reused.
>systemdatabase/6    07.20 15:05:05.987    IdcServer-39527    Parameters: ([indexName:FT_IdcText1],[tableName:IdcText1],[queryText:(SDATA(sddDocName LIKE 'SAM1059638'))],
>systemdatabase/7    07.20 15:05:06.003    IdcServer-39527    (start) SELECT 1 FROM DUAL
>systemdatabase/6    07.20 15:05:06.003    IdcServer-39527    0.83 ms. SELECT 1 FROM DUAL[Executed. Returned row(s): true]
>systemdatabase/6    07.20 15:05:06.003    IdcServer-39527    !csDbUnableToExecuteCallableQuery,CotsSearchQuery!$ORA-06550: line 1\, column 7:
 systemdatabase/6    07.20 15:05:06.003    IdcServer-39527    PLS-00201: identifier 'CONTENT_SEARCH.SEARCH_WITH_RESULTSETINTERFACE' must be declared
 systemdatabase/6    07.20 15:05:06.003    IdcServer-39527    ORA-06550: line 1\, column 7:
 systemdatabase/6    07.20 15:05:06.003    IdcServer-39527    PL/SQL: Statement ignored

...................
>services/3    07.20 15:05:06.003    IdcServer-39527    !csUserEventMessage,weblogic,server363!$ intradoc.common.ServiceException: !csSearchUnableToReturnResults
 services/3    07.20 15:05:06.003    IdcServer-39527    *ScriptStack GET_SEARCH_RESULTS
 services/3    07.20 15:05:06.003    IdcServer-39527    3:getSearchResults,**no captured values**
******************************************************************************

Solution for this error is to run the contentprocedures.sql on the WebCenterContent (UCM) schema.

This script can be found in the below location.
\\MIDDLEWARE_HOME\ORACLE_HOME\ucm\idc\database\oracle\admin