PLSQL Crash on Win7

[MartinF]

Hi,
I am working with PLSQL Dev 9.0.3.1641 on Windows 7. When querying some table that contain AQ type column, PLSQL crash. I tested it on WinXP with same version of PLSQL Dev and it work fine.

Here is the code to reproduce it if you want to try it

begin
  sys.dbms_aqadm.create_queue_table(
    queue_table => 'JMS_TEXT_MESSAGE_MFA',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
    sort_list => 'PRIORITY, ENQ_TIME',
    compatible => '10.0.0');
end;
/

begin
  sys.dbms_aqadm.create_queue(
    queue_name => 'REPORTING_QUEUE_MFA',
    queue_table => 'JMS_TEXT_MESSAGE_MFA',
    queue_type => sys.dbms_aqadm.normal_queue,
    max_retries => 0,
    retry_delay => 0,
    retention_time => 259200,
    comment => 'Retention=3 jours');
end;
/

begin
   DBMS_AQADM.START_QUEUE(queue_name => 'REPORTING_QUEUE_MFA');
end;
/

From here you can query the table and everything work fine because the table is empty.

select * from JMS_TEXT_MESSAGE_MFA;

now you can add record in the table

declare
        v_enqueue_options dbms_aq.enqueue_options_t;
        v_message sys.aq$_jms_text_message;
        v_message_properties dbms_aq.message_properties_t;
        v_message_id raw(1000);
    begin
        v_message := sys.aq$_jms_text_message.construct;

        v_message.set_string_property('Description'               ,'Test 123');
        v_message.set_string_property('Application'               ,'Test');
        v_message.set_string_property('Client'                    ,user);
        v_message.set_string_property('Environnement'             ,'DEV');

        dbms_aq.enqueue(queue_name => 'reporting_queue_mfa',
                        enqueue_options => v_enqueue_options,
                        message_properties => v_message_properties,
                        payload => v_message,
                        msgid => v_message_id
                        );
        commit;
end;
/

Now if you run the query select * from JMS_TEXT_MESSAGE_MFA PLSQL should crash.
The problem seem to be when PLSQL expand the content of column USER_DATA because if I remove it from the select it work fine.

If you need more info let me know

Martin

 

[Marco Kalter]

This works fine for me.

Can you let me know if there are any error messages? Can you also let me know your Oracle Client and Server version? Note that the Oracle 8.0 client can crash when querying collection data types.

 

[MartinF]

Hi Marco,
I just solved my problem. Error seem to occur when using my 10g client. I installed 11g client and everything work fine.

Regards,

Martin