Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle Advance Queue - Dequeue callback not working

RaivisJan 28 2020 — edited Jan 29 2020

Hi, I have 2 schemas: (A and B) In schema A i created advance queue and dequeue callback function. From schema A I granted schema B to execute enqueue procedure. When i enqueue data from schema A -> all is working fine. When i call A.enqueue data procedure from schema B -> data are enqueued, but they remain in READY state.... The dequeue callback function isn't triggered. Here is test script:

create table test_table (id number, event_name varchar2(200), descr nvarchar2(200));

/

create or replace type test_type as object (id number, event_name varchar2(200), descr nvarchar2(200))

/

create or replace procedure test_proc(context  in raw,

                                      reginfo  in sys.aq$_reg_info,

                                      descr    in sys.aq$_descriptor,

                                      payload  in raw,

                                      payloadl in number) as

  v_dequeue_options    dbms_aq.dequeue_options_t;

  v_message_id         raw(16);

  v_payload_message    test_type;

  v_message_properties dbms_aq.message_properties_t;

begin

  v_dequeue_options.msgid         := descr.msg_id;

  v_dequeue_options.consumer_name := descr.consumer_name;

  v_dequeue_options.wait          := dbms_aq.no_wait;

  DBMS_AQ.DEQUEUE(queue_name         => 'event_queue',

                  dequeue_options    => v_dequeue_options,

                  message_properties => v_message_properties,

                  payload            => v_payload_message,

                  msgid              => v_message_id);

  insert into test_table

    (id, event_name, descr)

  values

    (v_payload_message.id, v_payload_message.event_name, v_payload_message.descr);

  commit;

end;

/

create or replace procedure enq_data(p_msg in varchar2) is

  l_enqueue_options    DBMS_AQ.enqueue_options_t;

  l_message_properties DBMS_AQ.message_properties_t;

  l_message_handle     raw(16);

  l_event_msg          test_type;

begin

  l_event_msg := test_type(2, p_msg, null);

  DBMS_AQ.enqueue(queue_name         => 'cp.event_queue',

                  enqueue_options    => l_enqueue_options,

                  message_properties => l_message_properties,

                  payload            => l_event_msg,

                  msgid              => l_message_handle);

  commit;

end;

/

begin

DBMS_AQADM.create_queue_table( 

   queue_table        =>  'event_queue_tab',

    --sort_list => 'COMMIT_TIME',

    multiple_consumers => false,

    message_grouping => sys.dbms_aqadm.none,

    compatible => '10.0.0',

    primary_instance => 0, 

    secondary_instance => 0,

    queue_payload_type =>  'test_type');

  DBMS_AQADM.create_queue( 

   queue_name         =>  'event_queue', 

   queue_table        =>  'event_queue_tab');

  DBMS_AQADM.start_queue( 

   queue_name         => 'event_queue', 

   enqueue            => TRUE);

end;

/

begin

  dbms_aq.register (

     sys.aq$_reg_info_list (

        sys.aq$_reg_info (user || '.' || upper('event_queue'),

                          dbms_aq.namespace_aq,

                          'plsql://' || user || '.test_proc',

                          hextoraw ('FF'))),

     1);

  commit;

end;

/

grant execute on enq_data to B;

grant select on test_table to B;

connect with user A

---- THIS PART IS FOR TESTING

DECLARE

BEGIN

  enq_data('sdadasd');

END;

/

select * from test_table;

connect with user B

---- THIS PART IS FOR TESTING

DECLARE

BEGIN

  A.enq_data('sdadasd');

END;

/

select * from A.test_table;

I see that records are in READY state:

select * from aq$event_queue_tab;


Why so....What am i doing wrong?

Comments

390020
Works ok in my environment (note I'm using python 2.5).

-----
$ ipython
Python 2.5.2 (r252:60911, Jul 31 2008, 17:28:52)
Type "copyright", "credits" or "license" for more information.

In [1]: import cx_Oracle as ora

In [2]: con_str = "i/me@mine"

In [3]: con = ora.connect(con_str)

In [4]: type(con)
Out[4]: <type 'cx_Oracle.Connection'>

In [5]: con = ora.Connection(con_str)

In [6]: type(con)
Out[6]: <type 'cx_Oracle.Connection'>
-----
Q: what is the output if you do:

type(connection_string)


?

675949
type(connection)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
NameError: name 'connection' is not defined
390020
I asked for connection_string, not connection. Also I'm assuming you're running it in the same python session you run the previous code you show us. Is that right?
675949
Here is the code.....

Python 2.6.1 (r261:67517, Dec 4 2008, 16:51:00) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
import cx_Oracle
connection_string = "system/mypass@GLOBAL"
connection = cx_Oracle.Connection(connection_string)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-24315: illegal attribute type
type(connection_string)
<type 'str'>
>>>




I am doing all this under the same python session.
390020
Since I was unable to find out what is happening, I forwarded your problem to the cx-oracle-users list. You can find an answer over there. Here's [the link|http://sourceforge.net/mailarchive/forum.php?forum_name=cx-oracle-users&max_rows=25&style=nested&viewmonth=200812&viewday=19].
730306
I encountered this problem today, and there was no answer on the web.
This is what I learned, although it is embarassing to admit. I had installed
the Ora11g version of cx_Oracle, but the database is Ora10g. I deleted
the Ora11g cx_Oracle files, installed the Ora10g version, and everything
worked.
&nbsp;&nbsp;&nbsp;&nbsp;--dang
1 - 6

Post Details

Added on Jan 28 2020
1 comment
327 views