Queue with built-in datatype [message #429326] |
Tue, 03 November 2009 04:04  |
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   |
 |
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
johnbach wrote on Tue, 03 November 2009 04:04How 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 #429337 is a reply to message #429334] |
Tue, 03 November 2009 04:35  |
 |
ramoradba
Messages: 2457 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
|
|
|