object XMLTYPE does not exist in queue-table in SQL-window

[Pzz]

I get an "Error ORA-04043 object MW4MHZ.XMLTYPE does not exist" when I select from a queue-table in the SQL-window.

The same select works without a problem in the command-window. The queue-table is working fine and no problems with Advanced queueing.

The queue-table is based on queue "MHZ_bericht_type" which is:
CREATE OR REPLACE TYPE MHZ_bericht_type AS OBJECT
( init_id INTEGER,
xml_bericht xmltype)

Any idea?

Support info:
PL/SQL Developer
Version 9.0.3.1641
117.74547 - 1 user license
Windows XP Professional 5.1 Build 2600 (Service Pack 3)

Physical memory : 2.096.616 kB (1.144.768 available)
Paging file : 3.315.584 kB (2.347.236 available)
Virtual memory : 2.097.024 kB (1.968.768 available)

Parameters
C:\Program Files\PLSQL Developer\plsqldev.exe

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False
Allow Multiple Connections: True

Plug-Ins
*PL/SQL Developer CompareSource Plug-In version 2003/12/03 (C:\Program Files\PLSQL Developer\PlugIns\CompareSource.dll)
*PL/SQL Documentation (plsqldoc) (C:\Program Files\PLSQL Developer\PlugIns\plsqldoc.dll)
*Toggle Read-Only v1.0 (C:\Program Files\PLSQL Developer\PlugIns\togglereadonly.dll)
(* is Active)

Homes
OraClient10g_home1 (C:\oracle\product\10.2.0\client_1)

DLLs
C:\oracle\product\10.2.0\client_1\bin\oci.dll

TNS File
C:\oracle\product\10.2.0\client_1\Network\Admin\tnsnames.ora

Using
Home: OraClient10g_home1
DLL: C:\oracle\product\10.2.0\client_1\bin\oci.dll
OCI: Version 10.2
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Character Sets
Character size: 1 byte(s)
CharSetID: 46
NCharSetID: 2000
Unicode Support: True
NLS_LANG: DUTCH_THE NETHERLANDS.WE8MSWIN1252
NLS_CHARACTERSET: WE8ISO8859P15
NLS_NCHAR_CHARACTERSET: AL16UTF16

 

[Pzz]

I also have a "normal" table with an XMLTYPE as a fieldtype, and selects on this table works fine in a SQL-window.

 

[Marco Kalter]

Can you update to 9.0.6 and try again?

 

[Pzz]

I just did. No changes....

 

[Marco Kalter]

Can you send me the creation DDL fr this table?

 

[Pzz]

Table is created by AQ as:
begin
sys.dbms_aqadm.create_queue_table(
queue_table => 'MHZ_QUEUE_TABLE',
queue_payload_type => 'MW4MHZ.MHZ_BERICHT_TYPE',
sort_list => 'ENQ_TIME',
compatible => '10.0.0',
primary_instance => 0,
secondary_instance => 0,
storage_clause => 'tablespace MW4MHZ pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited )');
end;

The DDL:

CREATE TABLE "MW4MHZ"."MHZ_QUEUE_TABLE"
( "Q_NAME" VARCHAR2(30),
"MSGID" RAW(16),
"CORRID" VARCHAR2(128),
"PRIORITY" NUMBER,
"STATE" NUMBER,
"DELAY" TIMESTAMP (6),
"EXPIRATION" NUMBER,
"TIME_MANAGER_INFO" TIMESTAMP (6),
"LOCAL_ORDER_NO" NUMBER,
"CHAIN_NO" NUMBER,
"CSCN" NUMBER,
"DSCN" NUMBER,
"ENQ_TIME" TIMESTAMP (6),
"ENQ_UID" VARCHAR2(30),
"ENQ_TID" VARCHAR2(30),
"DEQ_TIME" TIMESTAMP (6),
"DEQ_UID" VARCHAR2(30),
"DEQ_TID" VARCHAR2(30),
"RETRY_COUNT" NUMBER,
"EXCEPTION_QSCHEMA" VARCHAR2(30),
"EXCEPTION_QUEUE" VARCHAR2(30),
"STEP_NO" NUMBER,
"RECIPIENT_KEY" NUMBER,
"DEQUEUE_MSGID" RAW(16),
"SENDER_NAME" VARCHAR2(30),
"SENDER_ADDRESS" VARCHAR2(1024),
"SENDER_PROTOCOL" NUMBER,
"USER_DATA" "MW4MHZ"."MHZ_BERICHT_TYPE" ,
"USER_PROP" "SYS"."ANYDATA" ,
PRIMARY KEY ("MSGID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ" ENABLE
) USAGE QUEUE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ"
XMLTYPE COLUMN "USER_DATA"."XML_BERICHT" STORE AS CLOB (
TABLESPACE "MW4MHZ" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
OPAQUE TYPE "USER_PROP" STORE AS LOB (
ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

CREATE INDEX "MW4MHZ"."AQ$_MHZ_QUEUE_TABLE_I" ON "MW4MHZ"."MHZ_QUEUE_TABLE" ("Q_NAME", "STATE", "ENQ_TIME", "STEP_NO", "CHAIN_NO", "LOCAL_ORDER_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ" ;

CREATE INDEX "MW4MHZ"."AQ$_MHZ_QUEUE_TABLE_T" ON "MW4MHZ"."MHZ_QUEUE_TABLE" ("TIME_MANAGER_INFO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ" ;

CREATE UNIQUE INDEX "MW4MHZ"."SYS_C0036521" ON "MW4MHZ"."MHZ_QUEUE_TABLE" ("MSGID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ" ;

CREATE UNIQUE INDEX "MW4MHZ"."SYS_IL0000117653C00031$$" ON "MW4MHZ"."MHZ_QUEUE_TABLE" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ"
PARALLEL (DEGREE 0 INSTANCES 0) ;

CREATE UNIQUE INDEX "MW4MHZ"."SYS_IL0000117653C00032$$" ON "MW4MHZ"."MHZ_QUEUE_TABLE" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ"
PARALLEL (DEGREE 0 INSTANCES 0) ;

ALTER TABLE "MW4MHZ"."MHZ_QUEUE_TABLE" ADD PRIMARY KEY ("MSGID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MW4MHZ" ENABLE;

 

[Marco Kalter]

Thanks, we'll check it out.