Using Oracle Text Query With "Order By" In a Sql-Statement, Gives No Results (Doc ID 461004.1)

Last updated on SEPTEMBER 03, 2010

Applies to:

Oracle Text - Version: 10.2.0.1 to 10.2.0.3 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

We have a sql statement:

select "OBJECT_ID","CLASS_ID","SYSTEMNO","ORDERNAME","CREATOR","CREATE_DATE"
 from
 (select rownum as r,"OBJECT_ID","CLASS_ID","SYSTEMNO","ORDERNAME","CREATOR","CREATE_DATE"
   from
   (SELECT OBJECT_ID, CLASS_ID, SYSTEMNO, ORDERNAME, CREATOR, CREATE_DATE
    from BO_ORDERMAP where OBJECT_ID IN
    (select OBJECT_ID
     from
     (select score(1), OBJECT_ID
      from GENERIC_XML
       where contains(object_xml, 'classs_id=OrderMap', 1)>0))
 ORDER BY "SYSTEMNO" desc NULLS FIRST ))
where (r between 1 and 25);


which yields 0 rows.When the "order by" clause is removed, 25 rows are returned. Although if the table generic_xml is replaced by the regular table generic, 25 rows are returned.

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