This content has been marked as final. Show 9 replies
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?
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.
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';
I'm assuming you haven't changed any code between database migration?
Thanks for your great help.
Here is a demo snippet that produces the error:
The full error stack is
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, 'firstname.lastname@example.org', 0, NULL, NULL), v_msg_id); COMMIT; END;
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.
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 220.127.116.11 - can you upgrade?
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 18.104.22.168, 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.
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 22.214.171.124 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 126.96.36.199 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.
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 188.8.131.52, 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.