Oracle Error ORA-604 and ORA-01456 Observed When First Time Setting the Transaction Type to Read Only (Doc ID 408966.1)

Last updated on JULY 03, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.0.0.0.1 and later
HP 9000 HP-UX 11.0 (32 bit)
HP-UX PA-RISC (32-bit)
IBM 370 AIX
Oracle Solaris on SPARC (32-bit)
z*OBSOLETE: Microsoft Windows 2000
***Checked for relevance on 17-Feb-2010***
***Checked for relevance on 17-Aug-2011***
***Checked for relevance on 24-Jan-2013***
Checked for relevance on 29-Aug-2014


Symptoms

Setting the transaction type to read only and using the stored outline query throws the following error:

ORA-00604: error occurred at recursive SQL level 1
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

SQL> create or replace outline on
2 select * from emp;

Outline created.

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> set transaction read only;

Transaction set.

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction Cause Bug 3227413

An error stack on event 1456 shows this to be an update on OUTLN.OL$:

ORA-1456: may not perform insert/delete/update operation inside a READ ONLY transaction

Current SQL statement for this session:
update ol$ set flags=:2 where ol_name=:1

This update only happens at the first time ever a particular outline is used.

dm_oracle.pinlog shows:

E Tue Mar 14 10:03:22 2006 catera dm:22636 dm_subr.c(98):6851 1:STC0017387:UnknownProgramName:0:AWT-EventQueue-0:4507:1142352202: 0
ORACLE error: do_sql_mr_select: OCIStmtExecute: code 604, op 0
=ORA-00604: error occurred at recursive SQL level 1
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
E Tue Mar 14 10:03:22 2006 catera dm:22636 dm_subr.c(98):5442 1:STC0017387:UnknownProgramName:0:AWT-EventQueue-0:4507:1142352202: 0
dm_read_subrow_multi_select: do_sql_mr_select failed select obj_id0, rec_id, amount, discount, item_obj_DB, item_obj_ID0, item_o
bj_TYPE, item_obj_REV, bal_grp_obj_DB, bal_grp_obj_ID0, bal_grp_obj_TYPE, bal_grp_obj_REV, resource_id, impact_type from event_bal_i
mpacts_t where obj_id0 in (:1,:2,:3,:4)
E Tue Mar 14 10:03:22 2006 catera dm:22636 dm_subr.c(98):5350 1:STC0017387:UnknownProgramName:0:AWT-EventQueue-0:4507:1142352202: 0
dm_read_subrows_wild: dm_read_subrow_multiple_select failed
E Tue Mar 14 10:03:22 2006 catera dm:22636 dm_do_ops.c(59):2887 1:STC0017387:UnknownProgramName:0:AWT-EventQueue-0:4507:1142352202:0
XXX DDD do_rflds, record_id -1, rsvd 0, *errp 43

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