question

Kastaka avatar image
Kastaka asked

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?

tableddltype
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Andrew Mobbs avatar image
Andrew Mobbs answered

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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
Bother - it was a permissions issue. It works fine without a synonym if the permissions are set up properly. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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