9 Replies Latest reply: Sep 17, 2012 10:13 AM by 758358 RSS

    trouble moving que to XE

    stevepence
      Hi,

      We are moving a que implementation from
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      to Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit

      We created the que without issue but are getting

      ORA-01722 invalid number
      ORA-06512 at "SYS.DBMS_AQ", line 169

      when running our code that does a dbms_aq.enqueue


      Below are the definitions of the payload and que table.

      thanks for any suggestions

      Steve

      -- create payload type
      CREATE OR REPLACE TYPE "LOST_PASSWORD_PAYLOAD" AS OBJECT (
      create_date_time DATE,
      secure_email_address VARCHAR2(50),
      affiliated_person_id NUMBER(10),
      user_logon_name VARCHAR2(64),
      answer VARCHAR2(30)
      )
      /

      -- Create table
      begin
      sys.dbms_aqadm.create_queue_table(
      queue_table => 'AUTH_RECOVER.LOST_PASSWORD_QTAB',
      queue_payload_type => 'AUTH_RECOVER.LOST_PASSWORD_PAYLOAD',
      sort_list => 'ENQ_TIME',
      compatible => '8.1.3',
      primary_instance => 0,
      secondary_instance => 0,
      comment => 'table holding all the user logons that need to be ',
      storage_clause => 'tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited )');
      end;
      /
        • 1. Re: trouble moving que to XE
          758358
          Hi,

          Could you provide the code you are running for DQ and also the full error message as it is reported when the failure occurs?

          Also, could you try adding errorstack tracing to see if that sheds anymore light on where the problem occurs?
          conn / as sysdba
          alter system set events '1722 trace name errorstack level 3'; 
          --repeat the issue by connecting as the application user and running your DQ code
          
          
          --disable the event
          conn / as sysdba
          alter system set events '1722 trace name errorstack off';
          This will produce a tracefile under the diagnostic dest - in the first 30 or so lines of this file should be a section entitled "Current SQL Statement for this session" - post this too.

          I'm assuming you haven't changed any code between database migration?

          Thanks
          Paul
          • 2. Re: trouble moving que to XE
            stevepence
            Dear Paul,

            Thanks for your great help.

            Here is a demo snippet that produces the error:
            DECLARE
            
               enqueue_rec             dbms_aq.enqueue_options_t;
               message_properties_rec  dbms_aq.message_properties_t;
               v_msg_id                RAW(16);
               
               
            BEGIN
               
                  dbms_aq.enqueue(  'auth_recover.lost_password_queue', 
                                    enqueue_rec, 
                                    message_properties_rec, 
                                    NEW lost_password_payload(SYSDATE, 'foo@bar.com', 0, NULL, NULL), 
                                    v_msg_id);
            
               COMMIT;
                  
            END;
            The full error stack is

            ERROR at line 1:
            ORA-00604: error occurred at recursive SQL level 1
            ORA-01722: invalid number
            ORA-06512: at "SYS.DBMS_AQ", line 169
            ORA-06512: at line 10


            And the sql statement from the trace file:
            ----- Error Stack Dump -----
            ORA-01722: invalid number
            ----- Current SQL Statement for this session (sql_id=bb3r3t3p2x24b) -----
            insert into "AUTH_RECOVER"."LOST_PASSWORD_QTAB" (q_name, msgid, corrid, priority, state, delay, expiration, time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_uid, enq_tid, retry_count, exception_qschema, exception_queue, recipient_key, dequeue_msgid, user_data, sender_name, sender_address, sender_protocol, user_prop, cscn, dscn) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, 0, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25)
            ----- PL/SQL Stack -----
            ----- PL/SQL Call Stack -----
            object line object

            Was there anything else from the trace file you need me to post?

            Thanks again for your help.

            Steve
            • 3. Re: trouble moving que to XE
              758358
              Hi,

              May well be because of this:

              Bug 9735237 - Dump [under kxspoac] / ORA-1722 as SQL uses child with mismatched BIND metadata (Doc ID 9735237.8)

              Do you see anything in the database alert log around the same time that the problem occurs? In the errorstack trace file (under ----- Call Stack Trace -----) do you see the following function call? kxspoac

              This issue is fixed at 11.2.0.3 - can you upgrade?

              Thanks
              Paul
              • 4. Re: trouble moving que to XE
                stevepence
                Hi Paul,

                I don't see that string in my trace file. The alert log does have an entry for the invalid number error, but points to the same trace file that doesn't contain the string.

                We are pretty new to XE... no patches.. no upgrades...so ? I guess by upgrading you mean install 11.2.0.3, create new db, import, etc. I suppose I could, but I'd like to be a bit more sure first. Hmmmm, maybe free doesn't mean easy.

                Thoughts?

                Thanks,
                Steve
                • 5. Re: trouble moving que to XE
                  758358
                  You should be able to upgrade an XE database in the same way as normal (I'm not that familiar with XE itself) - you could create a new database and exp/imp or just install 11.2.0.3 and then use DBUA to upgrade the existing database (I think).

                  In terms of confirming the bug is being encountered I'd really need to see the full tracefile ... which is difficult.

                  It doesn't take too long to create a new database and a test queue to replicate the issue so perhaps you could install 11.2.0.3 alongside, create a new DB and see if the problem occurs again with a matching test case. If it doesn't then you know it is worth the effort.

                  Thanks
                  Paul
                  • 6. Re: trouble moving que to XE
                    stevepence
                    Hi Paul,

                    Thanks for your help with this. Much appreciated. I'll give 11.2.0.3 a try one way or the other.

                    Steve
                    • 7. Re: trouble moving que to XE
                      758358
                      Hi,

                      No problem - be interested in hearing how you get on.

                      Paul
                      • 8. Re: trouble moving que to XE
                        stevepence
                        Hi Paul,

                        I'm getting back to this now that my system's manager has set me up a box where I can try this. Now my problem is that I can't seem to find a 11.2.0.3 version of XE. Are you sure that XE is being released at that level?

                        Thanks,
                        Steve
                        • 9. Re: trouble moving que to XE
                          758358
                          Hi,

                          I think I may have given you bad info when it comes to the XE bit - I've never used it before so wasn't fully aware of the limitations.

                          We don't supply patching or upgrade support for XE from what I can see so unless you can use one of the full versions then I don't think this is an option.

                          You can download Oracle Enterprise Edition for your own purposes as a test so maybe you could try your scenario on that with 11.2.0.3, assuming the issue doesn't repeat at least you will know why.

                          Thanks and apologies I can't be of more help with the XE thing.
                          Paul