1 Reply Latest reply on May 12, 2006 2:03 PM by sanju2

    Error creating AQ table with message that has nested tables

    278203
      Configuration:
      Oracle 9i RDBMS

      I'm trying to set-up an AQ that contains a custom object type as the Message type. The custom object contains a nested table definition as follows:

      Custom_Type:
      FIELD_A VARCHAR2,
      FIELD_B VARCHAR2;

      Object_1
      FIELD_1 NUMBER
      FIELD_2 NUMBER
      FIELD_3 Custom_Type;

      When I try and create the Table for this, I get the following:

      DBMS_AQADM.CREATE_QUEUE_TABLE
      (
      QUEUE_TABLE => 'test'
      ,QUEUE_PAYLOAD_TYPE => 'TEST.Object_1'
      ,COMPATIBLE => '8.1'
      ,STORAGE_CLAUSE => ' TABLESPACE TEST_DATA'
      ,SORT_LIST => ''
      ,MULTIPLE_CONSUMERS => FALSE
      ,MESSAGE_GROUPING => 0
      );
      End;
      ORA-22913: must specify table name for nested table column or attribute
      ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2224
      ORA-06512: at "SYS.DBMS_AQADM", line 58
      ORA-06512: at line 2


      Thoughts?

      _mike                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
        • 1. Re: Error creating AQ table with message that has nested tables
          sanju2
          create type Custom_Type as object(FIELD_A VARCHAR2(10),FIELD_B VARCHAR2(10));

          create type Object_1 as object(FIELD_1 NUMBER,FIELD_2 NUMBER,FIELD_3 Custom_Type);

          BEGIN
          SYS.DBMS_AQADM.CREATE_QUEUE_TABLE(
          queue_table      =>      'Q_OTN_TAB',
          queue_payload_type      =>      'OBJECT_1', <-----------Upper Case
          sort_list           =>      'ENQ_TIME',
          compatible           =>      '8.1',
          primary_instance      =>      0,
          secondary_instance      =>      0,
          multiple_consumers     =>      FALSE);
          END;

          BEGIN
          SYS.DBMS_AQADM.DROP_QUEUE_TABLE(
          queue_table      =>      'Q_OTN_TAB');
          END;

          Regards,
          Sanjeev.

          Message was edited by:
          user453374