2.10 Managing Sequences

A sequence generates a serial list of unique numbers for numeric columns of a database table. You can create, browse, drop, alter a sequence and view a report.

Database sequences are generally used to populate table primary keys.

• Creating a Sequence
  Create a sequence using Object Browser.
• Browsing a Sequence
  Select a sequence from the Object Selection pane and view different reports about the sequence.
• Reports for Sequences
  Alternative views available when browsing a sequence in Object Browser.
• Dropping a Sequence
  Select a sequence from the Object Selection pane and click Drop.
• Altering a Sequence
  Select a sequence from the Object Selection pane and click Alter.

2.10.1 Creating a Sequence

Create a sequence using Object Browser.

To create a sequence:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. Click the Create icon.
3. From the list of object types, select Sequence.
4. For Define:

   1. Sequence Name - Enter the name of the sequence.

   2. Preserve Case - To have the final sequence name match the case entered in the Sequence Name field, click Preserve Case.

   3. Start With - Enter the number of the first sequence. The first reference to sequence_name.nextval returns this number.

   4. Minimum Value - Enter the minimum value this sequence can return.

   5. Maximum Value - Enter the maximum value this sequence can return.

   6. Increment By - Each call to sequence_name.nextval returns a value greater than the last, until the maximum value is reached. Enter the value used to increment to the next sequence number.

   7. Cycle - Select this option to restart the sequence number to the minimum value when the maximum value is reached. Note that this is not recommended if using the sequence for primary key creation.

   8. Number to Cache - For faster access, specify how many sequence values are stored in memory.

   9. Order - Specify ORDER to guarantee that sequence numbers are generated in order of request. This option is necessary if using Real Application Clusters (Oracle RAC).

5. Click Next.

   A confirmation page appears, which displays the SQL used to create the sequence.

6. Click Create Sequence.

2.10.2 Browsing a Sequence

Select a sequence from the Object Selection pane and view different reports about the sequence.

To browse a sequence:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Sequences.
3. From the Object Selection pane, select a sequence.

   The Object Details view appears.

4. Click the tabs at the top of the page to view different reports about the sequence.

2.10.3 Reports for Sequences

Alternative views available when browsing a sequence in Object Browser.

Table 2-5 describes all available reports for sequences.

Table 2-5 Available Reports for Sequences

View	Description
Object Details	Displays details about the current sequence. You can perform Alter and Drop in this view.
Grants	Displays a list of grants associated with the sequence. Grant details include grantee, privilege, and grant options. You can perform Grant and Revoke in this view.
Dependencies	Displays a list of objects that use (or depend) upon this sequence.
SQL	Displays the SQL necessary to re-create this sequence.

2.10.4 Dropping a Sequence

Select a sequence from the Object Selection pane and click Drop.

To drop a sequence:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Sequences.
3. From the Object Selection pane, select a sequence.

   The Object Details view appears.

4. Click Drop.

2.10.5 Altering a Sequence

Select a sequence from the Object Selection pane and click Alter.

To alter a sequence

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Sequences.
3. From the Object Selection pane, select a sequence.

   The Object Details view appears.

4. Click Alter.