Skip to Main Content
  • Questions
  • ORA-02070: database does not support ROWIDs in this context

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Sumaj.

Asked: July 29, 2020 - 7:59 am UTC

Answered by: Chris Saxon - Last updated: July 29, 2020 - 10:23 am UTC

Category: PL/SQL - Version: 19C

Viewed 100+ times

You Asked

We have a Business case to migrate Oracle 19C from 11G, and one of our batch job is failing with the below error after the migration to Engineering environment.

ORA-02070: database  does not support ROWIDs in this context


Existing query in our business logic is using rowid in the order by clause associated with ref cursor and if we remove the rowid from the sorting the query is working, but the same query is working without any issues in 11G.
We don't have any other reliable column in the table for the sorting, we tried without rowid and resulted in incorrect output.

Sample sql snippet:

select * from table 
(test_pgk.transform_test 
(cursor 
( 
select code,text from test_1 t order by code,rowid 
)))


You can refer more details in livesql

A quick help would be highly appreciated.

with LiveSQL Test Case:

and we said...

I'm not sure how you had this working in 11g.

Stripping down to the cursor expression in 11.2.0.4 gives me:

create table test_1 (code number, text varchar2(100));
insert into test_1 values (1,'t1');
insert into test_1 values (2,'t2');
insert into test_1 values (3,'t3');

select * from v$version;

BANNER                                                                         
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production    
PL/SQL Release 11.2.0.4.0 - Production                                          
CORE 11.2.0.4.0 Production                                                      
TNS for Linux: Version 11.2.0.4.0 - Production                                  
NLSRTL Version 11.2.0.4.0 - Production  

select     
  cursor  ( 
      select code,text
      from   test_1 t 
      order by code, rowid 
    ) 
from dual;

ORA-03001: unimplemented feature
ORA-02070: database  does not support ROWIDs in this context


The key error is the first one: ORA-03001: unimplemented feature. It seems ordering by rowid in a cursor expression is not supported! If you insist on doing this, you'll need to take this up with support to get this added.

In general ordering by rowid is unsafe. Various actions can change the rowid of a row, so this isn't guaranteed to give you rows in the same order every time.

We don't have any other reliable column in the table for the sorting

Is there no primary/unique key on the table?

To be sure of always getting rows in the same order, adding PK/UC columns to the end of the sort will ensure everything returns in the same sequence.

If your table doesn't have a PK/UC... you've probably got bigger things to worry about anyway.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.