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

Question

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.

also im getting a error while performing the frontend functionality related to this " java.sql.SQLException: ORA-00932: inconsistent datatypes: expected SCHEMA.TYP_DUMMY_TYPE_25MAY2017 got SCHEMA.TYP_DUMMY_TYPE_25MAY2017" . Also the TYP_DUMMY_TYPE_25MAY2017 has dependency to package .view and trigger . — set
– set, Commented May 26, 2017 at 5:58
Tip for future reference: please make it easier for us to figure out what's going on with clear object naming. Three different things with virtually the same dummy name is unnecessary. — APC
– APC, Commented May 26, 2017 at 6:56
And the comment regarding ORA-00932 is a separate question. The StackOverflow rule is one question per thread. So start a new question. Include all the relevant details. Link to this question if necessary. — APC
– APC, Commented May 26, 2017 at 6:58

APC · Accepted Answer · 2017-05-26 06:54:22Z

2

"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>

answered May 26, 2017 at 6:54

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

set Over a year ago

I have also done the same thing :alter TYPE "TYP_DUMMY_TYPE_25MAY2017" compile . but then also getting the error :"java.sql.SQLException: ORA-00932: inconsistent datatypes: expected SCHEMA.TYP_DUMMY_TYPE_25MAY2017 got SCHEMA.TYP_DUMMY_TYPE_25MAY2017"

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related