9 Replies Latest reply: May 28, 2013 12:48 AM by 1010281 RSS

    Create AQ table with invalid timezone

    701246
      Hello experts,

      I have a problem:
      When i create a new advanced queue table the standard timezone is +00:00 in SYSTEM.AQ$_QUEUE_TABLES.
      When i am enqueuing a message to the AQ the enq_time differences two hours with the sysdate. This is because we have a timezone +02:00.

      I have a few questions related to above problems:

      1. Is there a setting to create an AQ with the correct timezone offset(+02:00) ?
      2. When i update the AQ in SYSTEM.AQ$_QUEUE_TABLES with the correct timezone offset(+02:00) en restart database, nothing happens. The timezone is still +00:00.

      I have read metalink(bug 5711286) and forum and all i can find is dat above issue is fixed in db version 11.2.0.1 and above.

      Below the database specifications:

      Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

      Thanks in advance,

      Ronald
        • 1. Re: Create AQ table with invalid timezone
          758358
          Hi,

          Bug 5711286 was never fixed, the fix comes in 11.2.0.1 from elsewhere.

          When you update SYSTEM.AQ$_QUEUE_TABLES do you en-queue more messages and they still have the wrong timestamp ?

          Thanks
          Paul
          • 2. Re: Create AQ table with invalid timezone
            701246
            Hi Paul,

            Yes, that is correct. When i enqueue after updating the timezone in aq_tables it still have two hours difference.

            We have updated de DSTv4 to DSTv11 because we thought the timezones where not correct, but that didn't help.

            We also have changed the dbtimezone to +02:00.
            • 3. Re: Create AQ table with invalid timezone
              user648708
              Hello,
              When i update the AQ in SYSTEM.AQ$_QUEUE_TABLES with the correct timezone offset(+02:00) en restart database, nothing happens. The timezone is still >+00:00.
              Generally, it is not recommandable to make such changes manually in data dictionary objects. This is too dangerous!
              If you would like for instance to compare ENQ_TIME (e.g. with SYSDATE or whatever), you could use the following workaround:
              transform the AQ enqueue time to UTC (and SYSDATE as well).

              v_time := SYS_EXTRACT_UTC( CAST(v_message_properties.enqueue_time AS TIMESTAMP))

              Unfortunately, there is still no bugfix available (up to 11.2.0.2).

              Kind regards,

              WoG
              • 4. Re: Create AQ table with invalid timezone
                758358
                user648708 wrote:
                Unfortunately, there is still no bugfix available (up to 11.2.0.2).
                Hi,

                Sorry - what leads you to this conclusion? The information I've found suggests that these sort of timezone issues should be fixed in 11.2.0.1, is there a specific bug number you are aware of that remains unfixed?

                Thanks
                Paul
                • 5. Re: Create AQ table with invalid timezone
                  user648708
                  Hello,
                  what leads you to this conclusion? The information I've found suggests that these sort of timezone issues should be fixed in 11.2.0.1, is there a specific bug >number you are aware of that remains unfixed?
                  As Paul already mentioned, there is still no bugfix (and to my knowledge) up to 11.2.0.2. We use the latest patch and we still struggle also
                  with this problem. Do you know a hidden, unpublished bugfix ?

                  WoG
                  • 6. Re: Create AQ table with invalid timezone
                    701246
                    I guess it is this bug:

                    Bug 6016633 - Timezone problems in AQ (ENQ_TIME wrong) [ID 6016633.8]
                    • 7. Re: Create AQ table with invalid timezone
                      758358
                      Hi,

                      Yes that is the bug which is supposedly fixed in 11.2.0.1, if people are still having problems in this area at this release or higher then this should be raised with Oracle Support.

                      Thanks
                      Paul

                      Edited by: pdtill2508 on Sep 14, 2011 9:45 AM
                      • 8. Re: Create AQ table with invalid timezone
                        701246
                        I found this also [ID 429681.1], but as you can see in de patch list there is only a patch for version 11.1.0.7.6.

                        I will make a request by Oracle Support.

                        Thank you for your fast input!

                        Ronald
                        • 9. Re: Create AQ table with invalid timezone
                          1010281
                          -- Create q_table

                          begin
                          DBMS_AQADM.create_queue_table (
                          queue_table => 'CARDSHUB_ISO8583_QTAB' , queue_payload_type => 'SYS.XMLTYPE'
                          ,multiple_consumers => TRUE, message_grouping => DBMS_AQADM.none);
                          end;


                          --Create queue:-

                          begin
                          DBMS_AQADM.create_queue (
                          queue_name => 'CARDSHUB_ISO8583_Q', queue_table => 'CARDSHUB_ISO8583_QTAB');
                          end;


                          --     Start queue:-


                          begin
                          dbms_aqadm.start_queue (
                          queue_name => 'CARDSHUB_ISO8583_Q');
                          end;

                          --Add subscriber:-

                          begin
                          DBMS_AQADM.add_subscriber (
                          queue_name => 'CARDSHUB_ISO8583_Q',
                          subscriber => sys.aq$_agent ('BIZTALK_USER', NULL, NULL));
                          end;





                          ---     Function Creation:-

                          --Enqueue

                          DECLARE
                          enqueue_options dbms_aq.enqueue_options_t;
                          message_properties dbms_aq.message_properties_t;
                          message_handle RAW(16);
                          message XMLTYPE;

                          BEGIN
                          message := XMLType('<Sample whNo="100">
                          <Sample_text>Owned</Sample_text>
                          </Sample>');

                          dbms_aq.enqueue(queue_name => 'CARDSHUB_ISO8583_Q',
                          enqueue_options => enqueue_options,
                          message_properties => message_properties,
                          payload => message,
                          msgid => message_handle);

                          COMMIT;

                          end;



                          -- dequeue

                          DECLARE
                          dequeue_options dbms_aq.dequeue_options_t;
                          message_properties dbms_aq.message_properties_t;
                          message_handle RAW(16);
                          message XMLTYPE;

                          BEGIN
                          dequeue_options.wait := dbms_aq.no_wait;
                          dequeue_options.consumer_name := 'BIZTALK_USER';
                          dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
                          LOOP
                          DBMS_AQ.DEQUEUE(queue_name => 'CARDSHUB_ISO8583_Q',
                          dequeue_options => dequeue_options,
                          message_properties => message_properties,
                          payload => message,
                          msgid => message_handle);

                          --DBMS_OUTPUT.PUT_LINE ('Message :' || message);
                          dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
                          COMMIT;
                          end loop;

                          END;



                          declare
                          p_q_name_i VARCHAR2(50);
                          p_consumer_i VARCHAR2(50);
                          v_dequeue_options DBMS_AQ.dequeue_options_t;
                          v_message_properties DBMS_AQ.message_properties_t;
                          v_message_handle RAW (16);
                          v_message XMLTYPE;
                          p_message_o XMLTYPE;

                          BEGIN
                          p_q_name_i := 'CARDSHUB_ISO8583_Q';
                          p_consumer_i := 'BIZTALK_USER';
                          v_dequeue_options.wait := dbms_aq.no_wait;

                          v_dequeue_options.navigation := DBMS_AQ.first_message;

                          v_dequeue_options.consumer_name := p_consumer_i;


                          DBMS_AQ.dequeue (
                          queue_name => p_q_name_i, dequeue_options => v_dequeue_options, message_properties => v_message_properties
                          ,payload => v_message, msgid => v_message_handle
                          );



                          p_message_o := v_message;

                          --dbms_output.put_line('Message :' || p_message_o);

                          EXCEPTION

                          WHEN OTHERS THEN
                          dbms_output.put_line('Error_message');

                          END;

                          I've an issue in the above pasted code when i am enqueuing any message the time difference in the base table is 5:30 hrs , could you please tell us how and where to use CAST for the same..

                          Thanks
                          Vibhanshu