SQL::Schema::Sequence - An Oracle sequence


SQL::Schema::Sequence - An Oracle sequence


  my $sequence = SQL::Schema::Sequence->new(%attr);
  my $sql = $sequence->create_statement;
  print $sql;
  print "$sequence";


SQL::Schema::Sequence is a class for objects representing an Oracle sequence. The methods of an instanciated object do allow to access the information within a database's data dictionary and to represent them as SQL create statements and the like using the proper SQL dialect.


  $sequence = SQL::Schema::Sequence->new(%attr);

The new method instanciates a sequence object. The object is an in memory representation of a (possible) oracle sequence. The attributes are given as key value pairs by the hash %attr. Possible keys are:

  key            required?   value description
  schema_name    no          The name of the schema for
  sequence_name  yes         The name of the sequence (without a
                             preceeding schema name).
  min_value      no          number
  max_value      no          number
  increment_by   yes         number
  cycle_flag     no          either `Y' or `N'; default: `N'
  order_flag     no          either `Y' or `N'; default: `N'
  cache_size     yes         number
  start_with     yes         number

These keys (except schema_name and start_with) and their possible values correspond exactly to the data dictionary view user_sequences and are described within Oracle's Server Reference.

The value for start_with is the number the sequence should start with.

  $sequence = SQL::Schema::Sequence->select($dbh,$name);
  $sequence = SQL::Schema::Sequence->select($dbh,$name,$schema_name);

The select method fetches the attributes required by new from the database and returns the sequence object. (It calls new internally.) It takes the following arguments:

If the sequence with the name $name could not be found within the database, the method returns undef.

The method's arguments are as follows:

A database handle as defined by DBI(3).

The name of the sequence without preceeding schema name.

Optionally the name of the database schema.

Warning: The method introduces a bug. The value for start_with required by the new method is selecte as last_number + increment_by. If the resulting value is outside the inverval given by min_value and max_value this is not checked. This means: In extreme cases it might happen, that the statement produced by the create_statement method can not really be executed on by a database.


The following attribute methods do return the current value of the attributes (as handed over to the new method):

  $schema_name = $sequence->schema_name;
  $name = $sequence->name;
  $min_value = $sequence->min_value;
  $max_value = $sequence->max_value;
  $increment_by = $sequence->increment_by;
  $cycle_flages = $sequence->cycle_flags;
  $order_flags = $sequence->order_flags;
  $cache_size = $sequence->cache_size;
  $start_with = $sequence->start_with;

The return value of cycle_flag resp. order_flag is either Y or N. This is somewhat uncomfortable for perl programmers. You might want to use the following two methods instead:

  $cycle_flag = $sequence->cycle;
  $order_flag = $sequence->order;

They do return 1 resp. 0 where their corresponding attribute method returns Y resp. N.

  my $qname = $sequence->qualified_name;

Returns the qualified name of the sequence which is the concatenation of schema_name and sequence_name with a in between if schema_name has been set. Otherwise only sequence_name is returned.

  $sql = $sequence->create_statement;
  $sql = "$sequence";

Returns a string containing an SQL statements for creation of a sequence. This method is overloaded with the string operator. So the two examples above are equivalent.

  $sql = $sequence->drop_statement;

Returns a string containing an SQL statement that would drop this sequence.


The select method introduces a bug. This occurs if the value selected from the database for start_with is not valid. For more information see warning at select.


  SQL::Schema::Sequence is Copyright (C) 2000,
    Torsten Hentschel
    Windmuehlenweg 47
    44141 Dortmund
    Email: todd@bayleys.ping.de
  All rights reserved.
  You may distribute this package under the terms of either the GNU
  General Public License or the Artistic License, as specified in the
  Perl README file.


DBI(3), the SQL::Schema(3) manpage

 SQL::Schema::Sequence - An Oracle sequence