dbms_aqadm.create_queue_table problems

From: Randy Nichols <randynichols_at_yahoo.com>
Date: Tue, 05 Aug 2003 16:37:05 GMT
Message-ID: <R2RXa.1378$Q63.82551@newsread2.prod.itd.earthlink.net>

I'm using Oracle 9.2.0.3 (patched) and SQL*PLUS to set up an Oracle queue, but keep encountering a problem when trying to run the "dbms_aqadm.create_queue_table()" procedure.

This is what happens:

---

SQL> exec
dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo ad_type=>'aqadm.queue_message_type');
BEGIN
dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo ad_type=>'aqadm.queue_message_type'); END;

*

ERROR at line 1:
ORA-03113: end-of-file on communication channel

---

Basically, I get logged off somewhere during the process. I don't know if that is what causes the error message, or vice versa.

I am following verbatim an example I found on the web (Akadia AG).

These are the steps leading up to the error:

---

SQL> connect system/<password>@rms as sysdba Connected.
SQL> create role my_aq_user_role;

Role created.

SQL> grant create session, aq_user_role to my_aq_user_role;

Grant succeeded.

SQL> exec dbms_aqadm.grant_system_privilege(privilege=>'enqueue_any', grantee=>'my_aq_user_role',admin_option=>false);

PL/SQL procedure successfully completed.

SQL> exec
dbms_aqadm.grant_system_privilege(privilege=>'dequeue_any',grantee=>'my_aq_u ser_role',admin_option=>false);

PL/SQL procedure successfully completed.

SQL> create role my_aq_adm_role;

Role created.

SQL> grant connect, resource,aq_administrator_role to my_aq_adm_role;

Grant succeeded.

SQL> create user aqadm identified by aqadm;

User created.

SQL> grant my_aq_adm_role to aqadm;

Grant succeeded.

SQL> create user aquser identified by aquser;

User created.

SQL> grant my_aq_user_role to aquser;

Grant succeeded.

SQL> connect aqadm/aqadm;
Connected.
SQL> create type queue_message_type
  2 as object(
  3 no number,
  4 title varchar2(30),
  5 text varchar2(2000));
  6 /

Type created.

SQL> grant execute on queue_message_type to my_aq_user_role;

Grant succeeded.

SQL> exec
dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo ad_type=>'aqadm.queue_message_type');
BEGIN
dbms_aqadm.create_queue_table(queue_table=>'queue_message_table',queue_paylo ad_type=>'aqadm.queue_message_type'); END;

*

ERROR at line 1:
ORA-03113: end-of-file on communication channel

---

---

I'm having this problem on two separate Oracle 9.2.0.3 installations. I must be omitting something.

Is anyone familiar with this problem?

Thanks,

Randy Nichols Received on Tue Aug 05 2003 - 11:37:05 CDT