TYPE becomes INVALID after export/import

A very specific case: at a customer site, we had to refresh a database using expdp/impdp. After the refresh, some TYPEs got INVALID and couldn't be recompiled. The types themselves were not that complex, and not depending on any other objects.

To spoil the surprise, it turns out that the expdp for the database version we were using (12c) messed up the TYPE definition, because it expanded an object name in the definition itself. This in turn was caused by the use of a keyword "ALTER" in the TYPE. Funny thing is: we didn't actually use the keyword as such, but it was used as part of an attribute name.

For example:

CREATE TYPE testuser.test_type_with_keyword
as object
(
   name           varchar2(20),
   alternate_name varchar2(20),
   constructor function test_type_with_keyword

     (self in out nocopy test_type_with_keyword) 

     return self as result
)

not final
/

CREATE TYPE BODY testuser.test_type_with_keyword as

  constructor function test_type_with_keyword

    (self in out nocopy test_type_with_keyword)
    return self as result is
  begin
    return;
  end;
end;
/

This snippet contains ALTER as part of the "alternate_name". The export thinks it recognizes a keyword and expands the name in the definition. That way, the name in the TYPE does no longer match the name in the TYPE BODY and recompilation fails.

This can be reproduced using DBMS_METADATA, as that will result in the same name expansion, thus invalidating the TYPE. This is the result after import:

CREATE TYPE testuser.test_type_with_keyword
as object
(
   name           varchar2(20),
   alternate_name varchar2(20),
   constructor function test_type_with_keyword

     (self in out nocopy "TESTUSER"."TEST_TYPE_WITH_KEYWORD") 

     return self as result
)
not final

CREATE TYPE BODY testuser.test_type_with_keyword as
  constructor function test_type_with_keyword

    (self in out nocopy test_type_with_keyword)
    return self as result is
  begin
    return;
  end;
end;

Two possible workarounds:

• prefix the object reference in specification and body yourself
• don't use the keyword ALTER as part of any TYPE to avoid this expansion (there might be other keywords that are treated the same, but I haven't found any yet)

The good news is: this seems to be a 12c problem, it doesn't reproduce on 18c or 19c.

Complete example:

--

-- Create a normal type first

-- without any keyword, so this will be fine

--

set long 10000

CREATE OR REPLACE TYPE testuser.test_type_without_keyword
as object
(
   name     varchar2(20),
   alt_name varchar2(20),
   constructor function test_type_without_keyword

     (self in out nocopy test_type_without_keyword)

     return self as result
)
not final
/

CREATE OR REPLACE TYPE BODY testuser.test_type_without_keyword as
  constructor function test_type_without_keyword

    (self in out nocopy test_type_without_keyword)
    return self as result is
  begin
    return;
  end;
end;
/

select DBMS_METADATA.GET_DDL('TYPE','TEST_TYPE_WITHOUT_KEYWORD','TESTUSER') from dual;

/*

-- DBMS_METADATA results
CREATE OR REPLACE EDITIONABLE TYPE "TESTUSER"."TEST_TYPE_WITHOUT_KEYWORD"
as object
(
   name     varchar2(20),
   alt_name varchar2(20),
   constructor function test_type_without_keyword

     (self in out nocopy test_type_without_keyword)

     return self as result
)
not final
CREATE OR REPLACE EDITIONABLE TYPE BODY "TESTUSER"."TEST_TYPE_WITHOUT_KEYWORD" as
  constructor function test_type_without_keyword

    (self in out nocopy test_type_without_keyword)
    return self as result is
  begin
    return;
  end;
end;
*/

DROP TYPE testuser.test_type_without_keyword;

--
-- Test with the same type, 

-- but then with the ALTER keyword hidden in the code
--

CREATE OR REPLACE TYPE 

testuser.test_type_with_keyword
as object
(
   name           varchar2(20),
   alternate_name varchar2(20),
   constructor function test_type_with_keyword

     (self in out nocopy test_type_with_keyword)

     return self as result
)
not final
/

CREATE OR REPLACE TYPE BODY 

testuser.test_type_with_keyword as
  constructor function test_type_with_keyword

    (self in out nocopy test_type_with_keyword)
    return self as result is
  begin
    return;
  end;
end;
/

select DBMS_METADATA.GET_DDL('TYPE','TEST_TYPE_WITH_KEYWORD','TESTUSER') from dual;               

/*
-- DBMS_METADATA results
CREATE OR REPLACE EDITIONABLE TYPE 

"TESTUSER"."TEST_TYPE_WITH_KEYWORD"
as object
(
   name           varchar2(20),
   alternate_name varchar2(20),
   constructor function test_type_with_keyword

     (self in out nocopy "TESTUSER"."TEST_TYPE_WITH_KEYWORD" )

     return self as result
)
not final
CREATE OR REPLACE EDITIONABLE TYPE BODY

"TESTUSER"."TEST_TYPE_WITH_KEYWORD" as
  constructor function test_type_with_keyword

    (self in out nocopy test_type_with_keyword)
    return self as result is
  begin
    return;
  end;
end;
*/

DROP TYPE testuser.test_type_with_keyword;