One-off Patch Post installation step fails On SDO_INDEX_METHOD_10I with PLS-00538 (Doc ID 1203457.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - Version 10.2.0.4 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.

Symptoms

One of the problem scenario where you will see such problem happening is described below -

1. You have applied spatial patch 7003151.

2. You have applied cumulative spatial merge patch bundle 9398469 on top.

3. For some reason you decided to rollback patch bundle 9398469 which internally rolled back already existing spatial patch 7003151.

4. Now, you want to have patch 7003151 back on your database.

5. You are following the README instructions to apply patch 7003151. As part of post installation task, you ran -

conn as sysdba
alter session set current_schema=MDSYS;
@prvtsidx.plb

6. The script failed at the end and you received the errors mentioned below -

conn as sysdba
# alter session set current_schema=MDSYS;
# @@prvtsidx.plb

Warning: Type Body created with compilation errors.

Errors for TYPE BODY SDO_INDEX_METHOD_10I:

LINE/COL ERROR
-------- -----------------------------------------------------------------
180/56 PLS-00538: subprogram or cursor 'ODCIINDEXREWRITE' is declared in
an object type specification and must be defined in the object
type body


Commit complete.

Now SDO_INDEX_METHOD_10I type body has INVALID status in dba_objects. Manual compliation fails with the same error, i.e. -

SQL> alter type MDSYS.SDO_INDEX_METHOD_10I compile body;

Warning: Type Body altered with compilation errors.

SQL> show errors type body MDSYS.SDO_INDEX_METHOD_10I
Errors for TYPE BODY MDSYS.SDO_INDEX_METHOD_10I:

LINE/COL ERROR
-------- -----------------------------------------------------------------
180/56 PLS-00538: subprogram or cursor 'ODCIINDEXREWRITE' is declared in
an object type specification and must be defined in the object type body

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