AV selecting from table with custom data type

Toad for Oracle - Beta
1

Howdy,

In beta 13.2.0.230, when I "SELECT *" from a table in a 12.2.0.1 DB that contains a custom data type, I get an AV and Toad crashes. There's no .el generated and I can't copy from the error popup.

If I select every column individually except the custom data type, it works fine. If I select all of the columns including the custom data type, I get: "List index out of bounds (26270)". The "SELECT *" causes the AV.

I can't offer a repeatable piece of code because the table is in the repository for Oracle EM 13.3. It's SYSMAN.EM_EVENT_BUS_TABLE. The column name is USER_DATA with a data type of EM_EV_BUS_MSG, and that type object is in the SYSMAN_TYPES schema with a local synonym in the SYSMAN schema. The other anomaly about this table is it contains an ANYDATA column as well.

I don't normally need to view this table data, except I'm looking to upgrade the repository to 19c and this column is flagged as needing some TLC prior to the upgrade.

Thanks!
Rich

1 Like
2

Hey Rich,

I don't have a DB with that schema, but ANYDATA inside of a user-defined type is most likely the culprit. I was able to reproduce it like this:

drop table type_with_anydata purge;

CREATE OR REPLACE TYPE Cust_Address_Type_ANY
AS OBJECT
    ( street_address     VARCHAR2(40)
    , postal_code        VARCHAR2(10)
    , city               VARCHAR2(30)
    , state_province     VARCHAR2(10)
    , country_id         CHAR(2)
    , new_dev_column     CHAR(2)
    , something          sys.anydata
    )
/

create table type_with_anydata
(col1 number,
 col2 Cust_Address_Type_ANY);

Insert into TYPE_WITH_ANYDATA
   (COL1, COL2)
 Values
   (1, CUST_ADDRESS_TYPE_ANY('1',NULL,NULL,NULL,NULL,NULL,SYS.ANYDATA.convertVarchar2('This is some data')));
   
Insert into TYPE_WITH_ANYDATA
   (COL1, COL2)
 Values
   (2, CUST_ADDRESS_TYPE_ANY('1',NULL,NULL,NULL,NULL,NULL,SYS.ANYDATA.convertNumber(1234567890)));
   
Insert into TYPE_WITH_ANYDATA
   (COL1, COL2)
 Values
   (3, CUST_ADDRESS_TYPE_ANY('1',NULL,NULL,NULL,NULL,NULL,SYS.ANYDATA.convertDate(TO_DATE('01-JAN-2012','DD-MON-YYYY'))));
commit;

and then in the Editor:

select t.*, t.rowid
from type_with_anydata t;

All I can tell you for now is don't select from that column. I'll log it. Hopefully we can get it fixed for 13.3.

1 Like
3

I tried to see if there was any ANYDATA columns in that type, or its multiple levels of subtypes, but I gave up. There's >50 type dependencies on the TYPE spec and >100 on the body.

I'll be content with waiting to test out your fix. :slight_smile:

Thanks, John!
Rich

4

Oh, you said The other anomaly about this table is it contains an ANYDATA column as well.

But you meant that the table contains an ANYDATA column in addition to the user-defined type!

I took it to mean that the ANYDATA was inside of the type.

Sounds like what I found is not what you described.

5

But there could be an ANYDATA buried in there. But I think it's more likely to be nested types with a mix of standard types and arrays of other user-defined types.

Frankly, it looks like a mess to me. It seems something with a JSON or XML structure would have been more straightforward...

Thanks!

6

Hi Rich, John,

Maybe first normal form too! :slight_smile:

Cheers,
Norm.