Searching with apostrophe causes an ORA-00907 error (Doc ID 736894.1)

Last updated on NOVEMBER 03, 2016

Applies to:

Oracle WebCenter Content - Version: 10.1.3.3.2 and later   [Release: 10gR3 and later ]
Information in this document applies to any platform.

Symptoms

-- Problem Statement:
Searching for documents with an apostrophe causes an error and does not return results.

Type search phrase like "Oracle's Compliance" into Title search field and you get this error:

Unable to retrieve search results. Unable to retrieve search results. Unable to create result set
for query 'SELECT IdcColl2.dID,
IdcColl2.dDocName, dDocTitle, dDocType, dRevisionID, dSecurityGroup, dDocAuthor, dDocAccount,
dRevLabel, dFormat, dOriginalName, dExtension,
dWebExtension, dInDate, dOutDate, dPublishType, dRendition1, dRendition2, VaultFileSize,
WebFileSize, URL, dFullTextFormat, dFullTextCharset,
DocMeta.*, '' as rUserRating, '' as rdUser, '' as rComments, ratingAverages.rtotalRaters,
ratingAverages.rAverageRating
FROM IdcColl2, DocMeta, RatingAverages
WHERE IdcColl2.dID=DocMeta.dID AND IdcColl2.dDocName=ratingAverages.rdDocName(+) AND
(((((UPPER(dDocTitle) LIKE UPPER('%Oracle's%') AND dDocTitle
IS NOT NULL AND UPPER(dDocTitle) LIKE UPPER('%Compliance%') AND dDocTitle IS NOT NULL)))) AND
(((UPPER(dDocAccount) LIKE UPPER('Public%') AND
dDocAccount IS NOT NULL) OR (UPPER(dDocAccount) LIKE UPPER('') AND dDocAccount IS NOT NULL) OR
(dDocAccount IS NULL) OR (UPPER(dDocAccount) LIKE
UPPER('Private%') AND dDocAccount IS NOT NULL)))) ORDER BY dInDate Desc'. ORA-00907: missing right
parenthesis

-- Steps To Reproduce:
Type search phrase like "Oracle's Compliance" into Title search field and you get this error:

Unable to retrieve search results. Unable to retrieve search results. Unable to create result set
for query 'SELECT IdcColl2.dID,
IdcColl2.dDocName, dDocTitle, dDocType, dRevisionID, dSecurityGroup, dDocAuthor, dDocAccount,
dRevLabel, dFormat, dOriginalName, dExtension,
dWebExtension, dInDate, dOutDate, dPublishType, dRendition1, dRendition2, VaultFileSize,
WebFileSize, URL, dFullTextFormat, dFullTextCharset,
DocMeta.*, '' as rUserRating, '' as rdUser, '' as rComments, ratingAverages.rtotalRaters,
ratingAverages.rAverageRating
FROM IdcColl2, DocMeta, RatingAverages
WHERE IdcColl2.dID=DocMeta.dID AND IdcColl2.dDocName=ratingAverages.rdDocName(+) AND
(((((UPPER(dDocTitle) LIKE UPPER('%Oracle's%') AND dDocTitle
IS NOT NULL AND UPPER(dDocTitle) LIKE UPPER('%Compliance%') AND dDocTitle IS NOT NULL)))) AND
(((UPPER(dDocAccount) LIKE UPPER('Public%') AND
dDocAccount IS NOT NULL) OR (UPPER(dDocAccount) LIKE UPPER('') AND dDocAccount IS NOT NULL) OR
(dDocAccount IS NULL) OR (UPPER(dDocAccount) LIKE
UPPER('Private%') AND dDocAccount IS NOT NULL)))) ORDER BY dInDate Desc'. ORA-00907: missing right
parenthesis


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms