My Oracle Support Banner

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

Last updated on JANUARY 03, 2023

Applies to:

Oracle WebCenter Content - Version 10.0 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.