Home » SQL & PL/SQL » SQL & PL/SQL » Queue with built-in datatype (10.2.0.1.0,RHEL)
icon5.gif  Queue with built-in datatype [message #429326] Tue, 03 November 2009 04:04 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
How to create Oracle Advanced Queue with system/built-in data type.

This is how i do now
CREATE OR REPLACE TYPE MY_TYPE AS OBJECT(pin VARCHAR2(15));
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table =>'PIN_QT',queue_payload_type => 'MY_TYPE');

DBMS_AQADM.CREATE_QUEUE (queue_name=>'pin_q',queue_table=>'pin_qt');


Is it possible to specify varchar2(15) directly in queue_payload_type without using a new type or RAW?
Re: Queue with built-in datatype [message #429334 is a reply to message #429326] Tue, 03 November 2009 04:30 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
johnbach wrote on Tue, 03 November 2009 04:04
How to create Oracle Advanced Queue with system/built-in data type.

This is how i do now
CREATE OR REPLACE TYPE MY_TYPE AS OBJECT(pin VARCHAR2(15));
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table =>'PIN_QT',queue_payload_type => 'MY_TYPE');

DBMS_AQADM.CREATE_QUEUE (queue_name=>'pin_q',queue_table=>'pin_qt');


Is it possible to specify varchar2(15) directly in queue_payload_type without using a new type or RAW?

SQL> CREATE OR REPLACE TYPE MY_TYPE AS OBJECT(pin VARCHAR2(15));
  2  /

Type created.

SQL> exec DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table =>'PIN_QT',queue_payload_type => 'MY_TYPE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_AQADM.CREATE_QUEUE (queue_name=>'pin_q',queue_table=>'pin_qt');

PL/SQL procedure successfully completed.

SQL> exec DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table =>'PIN_QT',queue_payload_type => 'varchar2(15)');
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table =>'PIN_QT',queue_payload_type => 'varchar2(15)'); END;

*
ERROR at line 1:
ORA-24000: invalid value varchar2(15), QUEUE_PAYLOAD_TYPE should be of the form
[SCHEMA.]NAME
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2826
ORA-06512: at "SYS.DBMS_AQADM", line 58
ORA-06512: at line 1


SQL> exec DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table =>'PIN_QT',queue_payload_type => varchar2(15));
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table =>'PIN_QT',queue_payload_type => varchar2(15)); END;

                                                                                 *
ERROR at line 1:
ORA-06550: line 1, column 82:
PLS-00222: no function with name 'VARCHAR2' exists in this scope
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>


No Its not possible.
Sriram.
Re: Queue with built-in datatype [message #429336 is a reply to message #429326] Tue, 03 November 2009 04:31 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
Hi,

I believe AQ can handle OBJECT type ONLY.

In case you figure out how to use primitive data type in AQ, Pls let us know.

Thanks,
Idris
Re: Queue with built-in datatype [message #429337 is a reply to message #429334] Tue, 03 November 2009 04:35 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
For more reference http://www.psoug.org/reference/aq_demo1.html


Quote:
** +----------------------------------------------------------------------------+
** | CREATE QUEUE TABLE |
** | -------------------------------------------------------------------------- |
** | -> (QUEUE_TABLE) - Name of the queue table to create. |
** | -> (QUEUE_PAYLOAD_TYPE) - Name of either the object type or RAW. |
** | |
** | NOTE: The "create_queue_table" procedure will also create the following |
** | items: |
** | |
** | AQ$QUEUE_TABLE_NAME - A read-only view for information on the queue |
** | table. |
** | AQ$QUEUE_TABLE_NAME_E - A default exception queue. |
** | AQ$QUEUE_TABLE_NAME_T - An index for time manager operations. |
** | AQ$QUEUE_TABLE_NAME_I - An index or index-organized table to handle |
** | dequeueing on queues with multiple consumers. |
** | |
** | ADDITIONAL NOTES: |
** | - Payload type can be either RAW or as a custom object type. |
** | - Maximum payload size is 32 KB. |
** | - When a user-defined object type is being used as a payload, the |
** | maximum number of attributes allowed for the object type is 900. |
** | - Messages must be in READY state to be dequeued unless a MSGID value is |
** | specified. Messages can be sorted for dequeue based on msgid or |
** | correlation values. |
** | |
** +----------------------------------------------------------------------------+
*/



/*
** +----------------------------------------------------------------------------+
** | CREATE QUEUE |
** | -------------------------------------------------------------------------- |
** | -> (QUEUE_NAME) - Name of the queue to create and place in the |
** | queue table (below). |
** | -> (QUEUE_TABLE) - Name of the queue table to store queue the (above) |
** | named queue in. |
** | -> (QUEUE_TYPE) - Type of queue to create. The types of queues to |
** | are NORMAL queues and EXCEPTION queues. |
** | -> (MAX_RETRIES) - Used to limit the number of times a dequeue with the |
** | REMOVE mode can be attempted on the message. The |
** | default is 0 which allows no retries. When the |
** | maximum number of retries is reached, the message is |
** | moved to the exception queue. The value is |
** | incremented when the application issues a rollback |
** | after executing the dequeue. |
** | -> (RETRY_DELAY) - Specifies the delay time, in seconds, before the |
** | message is scheduled for processing again after an |
** | application rollback. The default value is 0, which |
** | allows a message to be retried as soon as possible. |
** | If MAX_RETRIES is set to 0, the RETRY_DELAY argument |
** | will have no effect. |
** | -> (RETENTION_TIME) - |
** | -> (DEPENDENCY_TRACKING) - |
** | -> (COMMENT) - Assign a comment to the queue. |
** | -> (AUTO_COMMIT) - If you set the AUTO_COMMIT argument to 'TRUE', the |
** | current transaction, if any, will be committed |
** | before the operation is carried out. This is the |
** | default action. If you set the AUTO_COMMIT argument |
** | to 'FALSE', the operation will be part of the |
** | current transaction and will become persistent only |
** | when the user issues a COMMIT. |
** +----------------------------------------------------------------------------+
*/

Please go through this too.....http://www.psoug.org/reference/aq_demo2.html


Is this really related to this forum(sqlplus) ?

[Updated on: Tue, 03 November 2009 06:21]

Report message to a moderator

Previous Topic: Interview questions
Next Topic: Left join problem
Goto Forum:
  


Current Time: Fri Dec 02 16:33:31 CST 2016

Total time taken to generate the page: 0.46197 seconds