ORA-00902 Invalid Datatype when creating table with cross-schema type - why is this happening?

Question

question

Kastaka asked Jan 21, '10

ORA-00902 Invalid Datatype when creating table with cross-schema type - why is this happening?

This code gives me ORA-00902:

CREATE TYPE SchemaA.TypeForColumn AS OBJECT (col NUMBER);
/
CREATE TABLE SchemaB.ColumnDependsOnType (col SchemaA.TypeForColumn);

This very similar code doesn't:

CREATE TYPE SchemaA.TypeForColumn AS OBJECT (col NUMBER);
/
CREATE TABLE SchemaA.ColumnDependsOnType (col SchemaA.TypeForColumn);

I know that you can use some system types like this (e.g. ORDSYS.ORDAudio) - is it the case that you can't use custom types that are defined in other schemas, or am I doing something else wrong?

table ddl type

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-21T11:30:51Z

Andrew Mobbs answered Jan 21, '10

From memory, you need to create a synonym for the type in your own schema and reference that. I'll go and see if I can find some documentation.

Edit: For future reference; my recollection was incorrect, synonyms are not required. See comments.

Ah - other thing to check is that permissions are OK, and User A has permission on the type.

Try:

GRANT EXECUTE ON TypeForColumn TO UserA

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA commented · Jan 21, 2010 at 12:31 PM

A follow-up question, then... If you change the datatype in the alien schema, must you also update the synomym in the local schema?

0 ·

Kastaka commented · Jan 21, 2010 at 01:44 PM

Bother - it was a permissions issue. It works fine without a synonym if the permissions are set up properly. Thanks!

0 ·

question