7 Replies Latest reply on Jul 14, 2015 10:57 PM by gaverill

    Getting error "Invalid type oid" after dropping attribute of persisted object type

    2943636

      Hello there,

       

      I want to drop an attribute of an existing object type which is used in a complex type structure which is stored as a table column.

      After i have done that querying the table generates the error "Invalid type oid".

       

      The problem can be reproduced with the following script:

       

      CREATE TYPE base_type

      AS

        OBJECT

        (

          dummy NUMBER (1) ) NOT FINAL NOT INSTANTIABLE ;

        /

       

      CREATE type string_array IS VARRAY ( 100 ) OF VARCHAR2 (500 CHAR) ;

      /

       

      CREATE TYPE mod_par

      AS

        OBJECT

        (

          a1      NUMBER (12) ,

          a2      NUMBER (12) ,

          a3      INTEGER ,

          a_to_drop   VARCHAR2 (500 CHAR) ,

          a5      NUMBER (6,3) ,

          a6      NUMBER ,

          a7      string_array ,

          a8      VARCHAR2 (500 CHAR) ,

          a9      NUMBER (12) ,

          a10     NUMBER (12) ,

          a11     NUMBER (6,3) ,

          a12     NUMBER (12) ,

          a13     INTEGER ,

          a14     NUMBER ,

          a15     NUMBER ,

          a16     INTEGER ,

          a17     NUMBER (12) ,

          a18     VARCHAR2 (500 CHAR) ) FINAL ;

        /

       

      CREATE TYPE one_result

      AS

        OBJECT

        (

          a1 number(1) ,

          a2 number(1) ,

          a3 mod_par ,

          a4 NUMBER (6,3) ,

          a5   VARCHAR2 (500 CHAR) ) FINAL ;

        /

       

      CREATE TYPE one_result_array IS VARRAY ( 100 ) OF one_result ;

      /

       

      CREATE TYPE results

      AS

        OBJECT

        (

          a1 number(1) ,

          a2 TIMESTAMP (3) ,

          a3 TIMESTAMP (3) ,

          a4 VARCHAR2 (500 CHAR) ,

          a5 VARCHAR2 (500 CHAR) ,

          a6 one_result_array ) FINAL ;

        /

       

      create TYPE sub_type UNDER base_type

      (

        a1 number(1) ,

        a2 results ) FINAL ;

      /

       

       

      create table test1 (

        a1 base_type

        );

       

        insert into test1 values(

          sub_type(1, null,

            results(null, null, null, null, null,

              one_result_array(

                one_result(null, null, mod_par(1, 2, 3, 'bla 1', 4, 5, null, 'bla 2', 6, 7, 8, 9, 10, 11, 12, 13, 14, 'bla 3'), 10, 'bla'),

                one_result(null, null, mod_par(2, 3, 4, 'hallo 1', 5, 6, null, 'hallo 2', 7, 8, 9, 10, 11, 12, 13, 14, 15, 'hallo 3'), 20, 'hallo')

              ))));

             

          commit;

             

        select * from test1;

         

        alter type mod_par drop attribute a_to_drop cascade not including table data;

       

        -- At this point reconnect to the DB and execute "select * from test1" again. You should now get the error SQL-Fehler: Interner Fehler: Invalid type oid

       

      After a reconnect try to query the table again with select * from test1. You will get the error "Invalid type oid".

       

      If you change the script by using sub_type instead of base_type as table column type it works.

       

      I use oracle database enterprise edition 11.2.0.3

       

      What am I doing wrong?

       

      Thanks in advance!

       

      Daniel

        • 1. Re: Getting error "Invalid type oid" after dropping attribute of persisted object type
          Zlatko Sirotic

          I do not see that you are doing something wrong.

           

          The documentation (Database Object-Relational Developer's Guide 11g Release 2) says:

          NOT INCLUDING TABLE DATA (Option of CASCADE)

          Leaves column data as is, does not change type version.

          If an attribute is dropped from a type referenced by a table, the corresponding column of the dropped attribute is not removed from the table.

          However, the metadata of the column is marked unused. If the dropped attribute is stored out-of-line (for example, VARRAY, LOB, or nested table attribute), the out-of-line data is not removed.

          (Unused columns can be removed afterward by using an ALTER TABLE DROP UNUSED COLUMNS statement.)

          This option is useful when you have many large tables and may run out of rollback segments if you convert them all in one transaction.

          This option enables you to convert the data of each dependent table later in a separate transaction (using an ALTER TABLE UPGRADE INCLUDING DATA statement).

           

          I think that this is a bug in SQL+.

          Which SQL+ is used, 11.2.0.3?

          Have you tried to see what happens in SQL Developer?

           

           

          Regards,

          Zlatko

          • 2. Re: Getting error "Invalid type oid" after dropping attribute of persisted object type
            2943636

            Thanks for your fast response.

             

            I was using SQL Developer already.

            Now I also tried with sqlplus. There I get a different error message: ora-30757 cannot access type information

             

            I am using SQL Developer version 4.0.2.15 and sqlplus version 11.2.0.3.0.

             

            Kind regards,

            Daniel

            • 3. Re: Getting error "Invalid type oid" after dropping attribute of persisted object type
              Zlatko Sirotic

              Test with 11.2.0.4:

               

              SQL*Plus: Release 11.2.0.4.0 Production

              Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

               

              SQL> select * from test1;

              ERROR:

              ORA-22337: the type of accessed object has been evolved

               

              Perhaps it would be more successful with the database 12c.

               

              Regards,

              Zlatko

              • 4. Re: Getting error "Invalid type oid" after dropping attribute of persisted object type
                2943636

                I don't have a 12c installed.

                Could anyone who has a 12c installed check please.

                 

                Thank you!

                 

                Regards,

                Daniel

                • 5. Re: Re: Getting error "Invalid type oid" after dropping attribute of persisted object type
                  gaverill
                  BANNER                                                                         
                  --------------------------------------------------------------------------------
                      CON_ID
                  ----------
                  Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production   
                          0
                                                                                                 
                  PL/SQL Release 12.1.0.2.0 - Production                                         
                          0
                                                                                                 
                  CORE    12.1.0.2.0    Production                                                     
                          0
                                                                                                 
                  TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                       
                          0
                                                                                                 
                  NLSRTL Version 12.1.0.2.0 - Production                                         
                          0
                                                                                                 
                  
                  5 rows selected.
                  Type created.
                  Type created.
                  Type created.
                  Type created.
                  Type created.
                  Type created.
                  Type created.
                  Table created.
                  1 row created.
                  Commit complete.
                  
                  A1                                               
                  --------------------------------------------------
                  (1)                                             
                  1 row selected.
                  Type altered.
                  Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                  With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
                  select * from test1
                                *
                  Error at line 1
                  ORA-30757: cannot access type information
                  
                  

                   

                  Gerard

                  • 6. Re: Re: Getting error "Invalid type oid" after dropping attribute of persisted object type
                    2943636

                    Thank you!

                     

                    Thus the bug doesn't seem to be fixed yet.

                    If you haven't any furthur suggestions I think I will contact the oracle support.

                     

                    Regards,

                    Daniel

                    • 7. Re: Getting error "Invalid type oid" after dropping attribute of persisted object type
                      gaverill

                      Nope, no other suggestions from my end...

                       

                      Gerard