CODEWITHSUNDEEP
oracle-databaseplsqluser-defined-types
set
set

Reputation: 1

Error while compiling the nested table type in oracle pl/sql

I have created a type like this:

create or replace TYPE "DUMMY_TYPE_25MAY2017"
AS OBJECT
(
   programdata     VARCHAR2(2000)
)

Which is used by a nested table type

create or replace TYPE "TYP_DUMMY_TYPE_25MAY2017" AS TABLE OF DUMMY_TYPE_25MAY2017;

and a table 

CREATE TABLE DUMMY_TABLE_25MAY17 OF DUMMY_TYPE_25MAY2017;

Then I altered the type DUMMY_TYPE_25MAY2017

ALTER TYPE DUMMY_TYPE_25MAY2017
   MODIFY ATTRIBUTE (programdata VARCHAR2(4000)) CASCADE;

But now when I compile the type "TYP_DUMMY_TYPE_25MAY2017 " I am getting the error

"error: ora-22308: operation not allowed on evolved type".

Please help me how to resolve this.

Upvotes: 0

Views: 4046

Answers (1)

APC
APC

Reputation: 146349

"But now if I compile the type "TYP_DUMMY_TYPE_25MAY2017 " im getting the error"

It depends how you compile the nested table object. This error occurs with create or replace. The trick is to use alter type ... compile instead:

Here's your set-up:

SQL> create or replace TYPE "DUMMY_TYPE_25MAY2017" AS OBJECT
  2  (programdata     VARCHAR2(2000));
  3  /

Type created.

SQL> create or replace TYPE "TYP_DUMMY_TYPE_25MAY2017" AS TABLE OF DUMMY_TYPE_25MAY2017;
  2  /

Type created.

SQL> CREATE TABLE DUMMY_TABLE_25MAY17 OF DUMMY_TYPE_25MAY2017;

Table created.
SQL>

Now apply the change.

SQL> ALTER TYPE DUMMY_TYPE_25MAY2017
  2     MODIFY ATTRIBUTE (programdata VARCHAR2(4000)) CASCADE;

Type altered.

SQL> create or replace TYPE "TYP_DUMMY_TYPE_25MAY2017" AS TABLE OF DUMMY_TYPE_25MAY2017;
  2  /
create or replace TYPE "TYP_DUMMY_TYPE_25MAY2017" AS TABLE OF DUMMY_TYPE_25MAY2017;
*
ERROR at line 1:
ORA-22308: operation not allowed on evolved type


SQL> alter TYPE "TYP_DUMMY_TYPE_25MAY2017" compile 
  2  /

Type altered.

SQL> insert into  DUMMY_TABLE_25MAY17 values ( DUMMY_TYPE_25MAY2017 ('this is a test'));

1 row created.

SQL>

Upvotes: 2

Related Questions