After upgrading the database to 11.2.0.4 and applying the latest Patch Set Update(October 2015 PSU) (11.2.0.4.8) , a database account could not perform any update or delete transaction on a table (that has context index) and received the following errors:

 

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

Solution:

There is a patch provided by Oracle to fix this OR perform the following “workaround” by recreating ctxsys.syncrn procedure:

SQL> sqlplus ‘/as sysdba’

SQL>alter session set current_schema=CTXSYS;

SQL>create or replace procedure syncrn (
  ownid IN binary_integer,
  oname IN varchar2,
  idxid IN binary_integer,
  ixpid IN binary_integer,
  rtabnm IN varchar2,
  srcflg IN binary_integer,
  smallr IN binary_integer
)
  authid definer
  as external
  name “comt_cb”
  library dr$lib
  with context
  parameters(
    context,
    ownid  ub4,
    oname  OCISTRING,
    idxid  ub4,
    ixpid  ub4,
    rtabnm OCISTRING,
    srcflg ub1,
    smallr ub1
);
/

// execute the following to recompile all database objects:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

//Then check that your database components are “valid”:

SQL> select * from dba_registry;

//check if there is any invalid objects under CTXSYS:

SQL>select owner, count(*) from dba_objects where status <>’VALID’ group by owner order by 1;

 

Remark: to find the patch set update applied on your database use the following query:

select * from sys.registry$history;