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.
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 contextThe 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 sortingIs 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.