Create a Sequence Script

Insert into table values ('Some Name',SeqID.NextVal);

• To use that value more than once, use CurrVal after the first.
• Can't be used in subqueries, DISTINCT, UNION, INTERSECT, MINUS, Order by, group by, having.

Define OnOff = Off

rem +--------------------------------------------------------------------------+
rem | Script ID:     seq.sql
rem |
rem | Purpose:       This creates a sequence that doesn't cycle.  I'm not error
rem |                checking -- I'll let Oracle tell that what's wrong.  If it
rem |                is a descending sequence, I'll put in the minus signs.
rem |
rem | Developer:     Lynn Tobias
rem | Script Date:   August 08, 2008
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual
rem |              
rem | Called by:     n/a
rem | Calls:         CrSeq.sql (generated in this script)
rem |
rem | Variables:     Asc_Dsc      - ascending/descending sequence
rem |                Cache        - choice for cache/nocache
rem |                Increment_By - how many to increment by
rem |                MaxMin       - max for ascending, min for descending
rem |                mval         - what was keyed for max/min question
rem |                NoOrder      - order/noOrder
rem |                OnOff        - Used for debugging
rem |                Schema       - Schema name
rem |                Seq_Name     - Sequence name keyed
rem |                Sign         - negative if descending
rem |                Start_With   - choice for value to start with
rem |              
rem | Output:        n/a
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem |              
rem +--------------------------------------------------------------------------+

Set Heading Off Echo &OnOff Verify &OnOff Feedback &OnOff Timing &OnOff Trimspool On

Prompt ******
Prompt * This creates a non-cycling sequence
Prompt ******
Prompt

--+- 1 -------------------------------------------------------------------------
--| Get the schema and the sequence name
--+-----------------------------------------------------------------------------

Accept Schema Default &_User -
    Prompt 'Enter SCHEMA or press Enter to accept &_User: '
Prompt

Accept Seq_Name -
    Prompt 'SEQUENCE Name to be created: '
Prompt
Select '***** This sequence already exists. It will be dropped. ******'
From All_Objects
Where Object_Type = 'SEQUENCE'
 And Owner       = Upper('&Schema')
 And Object_Name = Upper('&Seq_Name');

--+- 3 -------------------------------------------------------------------------
--| Find out Ascending or Descending so it's a positive or negative increment
--| and either Max or Min is used.
--+-----------------------------------------------------------------------------

Prompt
Accept Asc_Dsc Default Asc -
 Prompt 'Is this an ascending or descending sequence? (Default = Ascending) a/d: '
Prompt

Column Sign    New_Value Sign
Column MaxMin New_Value MaxMin

Set Term &OnOff

Select Case When Upper('&ASC_DSC') in ('A','ASC','ASCENDING')
          Then ' '             -- Ascending doesn't take a sign
          Else '-'             -- Descending needs a negative sign
      End as Sign,
     ------------------------------------------------------
     Case When Upper('&ASC_DSC') in ('A','ASC','ASCENDING')
          Then 'MAX'           -- Ascending is 'MAXVALUE'
          Else 'MIN'           -- Descending is 'MINVALUE'
      End as MaxMin
     ------------------------------------------------------
From Dual;

Set Term On

--+- 4 -------------------------------------------------------------------------
--| Get the answers to Start_with, Increment_by, Max/MinValue, Cache, order
--+-----------------------------------------------------------------------------

Accept Start_With    Default 1 -
 Prompt 'START WITH (1st number to be generated)  (Default = 1): '
Prompt

--+-----------------------------------------------------------------------------

Accept Increment_By  Default 1 -
 Prompt 'INCREMENT BY (interval between numbers)(Default = 1): '
Prompt

--+-----------------------------------------------------------------------------

Accept mval          Default NO&MaxMin.VALUE -
 Prompt '&MaxMin.VALUE The highest or lowest value that can be generated. (Default = No&MaxMin.Value): '
Prompt

--+-----------------------------------------------------------------------------

Accept Cache         Default 20 -
 Prompt 'CACHE (the # kept in memory for faster access) (Default = 20) or type ''No'' for NoCache: '
Prompt

--+-----------------------------------------------------------------------------

Accept NoOrder       Default NoOrder -
 Prompt 'ORDER (Important if a timestamp or RAC. (Default = NoOrder) o/n: ';
Prompt

--+- 5 -------------------------------------------------------------------------
--| Create the sequence code and spool
--+-----------------------------------------------------------------------------
--| Basic layout for reference
--| CREATE SEQUENCE [ schema. ]sequence
--|    [ { INCREMENT BY | START WITH } integer
--|    | { MAXVALUE integer | NOMAXVALUE }
--|    | { MINVALUE integer | NOMINVALUE }
--|    | { CYCLE | NOCYCLE }
--|    | { CACHE integer | NOCACHE }
--|    | { ORDER | NOORDER }
--+-----------------------------------------------------------------------------
Spool CrSeq.sql

Select --'Drop Sequence &Schema'  ||'.&Seq_Name;'                   ||chr(10)||
     'Create Sequence &Schema'||'.&Seq_Name'                    ||chr(10)||
     'Start With      &Sign&Start_With'                         ||chr(10)||
     'Increment By    &Sign&Increment_By'                       ||chr(10)||
     -------------------------------------------------------------
     Case When Upper('&mval')   in ('N','NO','NOMAXVALUE', 'NOMINVALUE')
          Then 'No&MaxMin'||'Value  NOCYCLE'
          Else '&MaxMin'||'Value &mval NOCYCLE'
      End                                                       ||chr(10)||
     -------------------------------------------------------------
     Case When Upper('&Cache')   in ('N','NO','NOCACHE')
          Then 'NoCache'
          Else 'Cache &Cache'
      End                                                       ||chr(10)||
     -------------------------------------------------------------
     Case When Upper('&NoOrder') in ('N','NO','NOORDER')
          Then 'NoOrder ;'
          Else 'Order ;'
      End 
From Dual;

Spool off

--+- 6 -------------------------------------------------------------------------
--| Drop the sequence (just in case), but don't display so no error posted.
--| Turn feedback on so they see when the sequence is created.
--+-----------------------------------------------------------------------------

Set Term &OnOff
Drop Sequence &Schema..&Seq_Name;

Set Term On Feedback On
@CrSeq

Undefine Asc_Dsc Sign Maxmin Mval Schema Start_With Increment_By Cache Noorder _Rc Seq_Name
Set Heading On Verify On