OCIDESCRIBEANY() Failed To Find An UDT Object When Sdo_geometry Prefixed With Schema Name Instead Of Public

(Doc ID 1334920.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms


In 11g, when a table with a column SDO_GEOMETRY is created within a specific schema lets say SCOTT. The type is created as SDO_GEOMETRY type, the db will automatically change the udt schema to PUBLIC instead, while for 10g, the column type remains as SDO_GEOMETRY

This is causing that a call to program "extract" in GoldenGate errors out with:

Source Context :
SourceModule : [ggdb.ora.udt]
SourceID : [/mnt/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.26_001_[46254]/perforce/src/gglib/ggdbora/ociudt.c]
SourceFunction : [checkForSynonym]
SourceLine : [365]

2011-03-10 08:47:34 ERROR OGG-00746 Error (100, no data found) selecting data in checkForSynonym.


That is caused because the SDO_GEOMETRY type is being prefixed with the schema that
created the table and not PUBLIC

Basically:
We need to fix it in 10g so that the column type will show up as PUBLIC.SDO_GEOMETRY instead
of SCOTT.SDO_GEOMETRY


create table mytab1( col1 int primary key, col2 sdo_geometry);

desc scott.mytab1

Name Null? Type
----------------------------------------- -------- ---------------------------
COL1                                      NOT NULL NUMBER(38)
COL2                                               SDO_GEOMETRY


Where as in 11g , PUBLIC is correctly prefixed.


desc scott.mytab1

Name Null? Type
----------------------------------------- -------- ---------------------------
COL1                                      NOT NULL NUMBER(38)
COL2                                               PUBLIC.SDO_GEOMETRY

Workaround:
Recreate the table prefixing the column with MDSYS or the owner of the object data type. Note that
this is not exclusive of SDO_GEOMETRY but any object datatype. Workaround not acceptable for
some customers  because they may have objects in the order of terabytes that they can not recreate.

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