This seems to work fine with the built-in Oracle client, but not with the Instant Client.
CREATE OR REPLACE TYPE test_type AS OBJECT ( val NUMBER );
CREATE TABLE test_table AS (
SELECT ANYDATA.convertObject( NEW test_type(1) ) AS val FROM dual
);
SELECT * FROM test_table;
>> SELECT * FROM test_queue_qtab
*
Error at line 0
ORA-22636: object or collection unpickling failed due to corrupt image
This also happens in the Data tab of the Description dialog of the queue table.
The built-in Oracle client has no problems with this:
Alternative way (I first encountered this with an Advanced Queue table):
-- Preparation
BEGIN
sys.dbms_aqadm.create_queue_table( 'TEST_QUEUE_QTAB', 'sys.ANYDATA' );
sys.dbms_aqadm.create_queue( 'TEST_QUEUE', 'TEST_QUEUE_QTAB' );
sys.dbms_aqadm.start_queue( 'TEST_QUEUE' );
END;
/
CREATE OR REPLACE TYPE test_type AS OBJECT ( val NUMBER );
DECLARE
v_enqueue_options dbms_aq.enqueue_options_t;
v_message_properties dbms_aq.message_properties_t;
v_message_handle RAW(16);
v_payload sys.ANYDATA := ANYDATA.convertObject( NEW test_type(1) );
BEGIN
dbms_aq.enqueue(
queue_name => 'TEST_QUEUE',
enqueue_options => v_enqueue_options,
message_properties => v_message_properties,
payload => v_payload,
msgid => v_message_handle
);
END;
-- Execution
SELECT * FROM test_queue_qtab;