Creating a Queue in Oracle

For this particular example, let us assume that the information being between application takes on the form of two fields, row_id and row_status. Given that, we will create this following object type, which will be used by the queue shortly.

create or replace type obj_stuff as object (
	row_id	number,
	row_status	varchar2(10)
);

With the object type created, we will first create a queue table based on this object type, followed by a queue.

declare
begin
	dbms_aqadm.create_queue_table (
		queue_table	=>	'myschema.stuff_queue_table',
		queue_payload_type	=>	'myschema.obj_stuff'
	);
	dbms_aqadm.create_queue (
		queue_name	=>	'myschema.stuff_queue',
		queue_table	=>	'myschema.stuff_queue_table'
	);
end;

With the script above, we now have the queue table and the queue set up. To start the queue, use the .start_queue script, as below.

declare
begin
	dbms_aqadm.start_queue (
		queue_name	=>	'myschema.stuff_queue',
		enqueue	=>	true
	);
end;