How does Oracle do this?

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 16 Aug 2006 08:28:37 -0700
Message-ID: <1155742117.711023.229980@m73g2000cwd.googlegroups.com>

For Oracle advanced queues, oracle has a procedure:

dbms_aq.dequeue

It accepts a user defined type as one of its parameters. You can define whatever type you want, and pass it into this procedure and it magically knows what to do with it.

I am trying to create api's to various queues in an application, and we have serveral different types of queue payloads. I have found myself writing a bunch of overloaded procedure each of which has the same internals like:

procedure dequeue( o_payload my_type_1);

procedure dequeue( o_payload my_type_2);

How can I do it the 'Oracle' way so that I can just write one of these functions or is Oracle using some insider tricks that we cannot use?

ie something like:

create or replace procedure dequeue( i_queue_name, o_payload ??sometype??)
is
begin
dbms_aq.dequeue(i_queue_name,

                              dequeue_opts,
                              ...
                              o_payload);

end;
/

declare
  v_mytype_1 my_type_1;
  v_mytype_2 my_type_2;
begin
  dequeue ( 'queue_1', v_mytype_1);
  dequeue ('queue_2', v_mytype_2);
end;
/

Thanks

Stephen. Received on Wed Aug 16 2006 - 10:28:37 CDT