CREATE QUEUE

CREATE QUEUE

Syntax

create_queue ::=

column_definition ::=

Prerequisites

It is necessary to be satisfied one or more of the following conditions in order to create table:

• The SYS user
• The user has the CREATE TABLE or CREATE ANY TABLE system privilege in own schema.
• The user has the CREATE ANY TABLE system privilege in another user's schema.

Description

This syntax either can configure the maximum length of messages that are inserted into a queue or allows the user to directly define a column in order to create a queue. It can also specifies the number of maximum records that can be stored in a queue table.

queue_name

This is used to specify the name of the queue. The maximum possible length of the queue name is 28 bytes.

size

This is used to set the maximum size (in bytes) of a message to be stored in the queue. This value can be set within the range from 1 to 32,000 bytes.

FIXED|variable_clause

This is used to specify how messages are saved. (For more information, please refer to the General Reference).

column_definition

This clause specifies the user-defined column. It refers to CREATE TABLE statement's column_definition and does not support column_constraint, crypt_clause and timestamp.

MAXROWS count

This is used to set the maximum number of records that can be stored in a queue table. This value can be set within the range from 1 to 4294967295 (or (2³²) -1). When not specified, it defaults to the maximum value of 4294967295.

Considerations

When a queue is created, an object having the name queue_name + "_NEXT_MSG_ID" is created in the database. Therefore, if any existing table, view, sequence, synonym or stored procedure has the same name as the queue to be created, or has the name queue_name + "_NEXT_MSG_ID", the CREATE QUEUE statement will return an error.

Examples

<Query> >Create the queue Q1, stipulating that the maximum message length is 40 bytes and that the maximum number of records is 1,000,000.

iSQL> CREATE QUEUE Q1(40) MAXROWS 1000000;
Create success.

<Query> Create 2 columns that can store values for numeric(5,2) type when creating a queue named Q1.

iSQL> CREATE QUEUE Q1(c1 numeric(5,2), c2 numeric(5,2));
Create success.

<Query> Create a queue named Q2 defining the user-defined column and delete it after writing messages into the pertinent column.

iSQL> CREATE QUEUE Q2(V1 VARCHAR(10), V2 INTEGER, V3 NUMERIC(5,3));
Create success.

iSQL> ENQUEUE INTO Q2(V1, V2, V3) VALUES ('abc', 1, 99.999);
1 row inserted.

iSQL> DEQUEUE V1, V2, V3 FROM Q2;
V1          V2          V3
----------------------------------------
abc         1           99.999
1 row selected.