Creating Sequences and Synonyms

Various other database objects are needed to support the main objects in the database (such as tables). Two such objects are sequences and synonyms.

Sequences

An Oracle sequence is a named sequential number generator. A sequence is often used to generate a unique key for the primary key of a table. A sequence object is owned by a single schema, but it can be used by other database users if the proper permissions are granted to the users.

sequence

A database structure that generates a series of numbers typically used to assign primary key values to database tables.

Sequences can begin and end with any value, can be ascending or descending, and can skip (increment) a specified number between each value in the sequence. The basic syntax for CREATE SEQUENCE is as follows:

CREATE SEQUENCE sequence_name    [START WITH starting_value]    [INCREMENT BY increment_value];

If all optional parameters are omitted, the sequence starts with one and increments by one, with no upper boundary.

Sequences are referenced in DML statements by using the syntax sequence_name.currval or sequence_name.nextval. The qualifier nextval retrieves the next value. The qualifier currval retrieves the most recent number generated without incrementing the counter. For example, here are some sample SELECT statements that access the sequence used for employee numbers, EMPLOYEES_SEQ:

select employees_seq.nextval from dual; NEXTVAL ----------        211 1 row selected. select employees_seq.nextval from dual; NEXTVAL ----------        212 1 row selected. select employees_seq.currval from dual; CURRVAL ----------        212 1 row selected.

The HR department has asked the DBA, Janice, to re-create the sequence for the EMPLOYEES table to start at 501 and increment by 10. Janice drops the old sequence and re-creates it:

drop sequence hr.employees_seq; Sequence dropped. create sequence hr.employees_seq    start with 501    increment by 10; Sequence created.

After the sequence has been created, the user HR inserts a record into the EMPLOYEES table as follows:

insert into employees    (employee_id, last_name, first_name, email,     hire_date, job_id) values    (employees_seq.nextval, ‘JUNDT’, ‘SUSAN’, ‘SJUNDT’,     ‘15-oct-02’,’ST_MAN’); 1 row created. select employee_id from employees where last_name = ‘JUNDT’; EMPLOYEE_ID -----------         501 1 row selected.

• The next time the employees_sesequence is used, the value returned will be 511.

Synonyms

A synonym is an alias for another database object, such as a table, sequence, or view. Synonyms provide easier access to database objects outside the user’s schema.

synonym

An alias assigned to a table, view, or other database structure. Synonyms can be either available to all users (public) or available only to one schema owner (private).

There are two kinds of synonyms: public and private. Public synonyms are available to all database users. A private synonym is available only in the session of the schema owner who created it.

Synonyms are useful in providing a common name to a database object, regardless of which username is logged in to the database. The temporary table created by Janice, the DBA, called TRAVEL_DEST must be qualified with the schema name if anyone other than Janice wants to access it. For example, if the user HR is connected to the database and no synonym has been specified, the table must be fully qualified:

insert into janice.travel_dest    values(101, 1201, 320.50, 988.00);

The syntax for creating a synonym is as follows:

CREATE [PUBLIC] SYNONYM synonym_name    FOR [schema.]object_name; 

To facilitate easy access to the table TRAVEL_DEST, Janice creates a public synonym for the table:

create public synonym travel_dest for travel_dest; Synonym created.

What happens if a user has a private synonym called TRAVEL_DEST, or worse yet, his or her own table is called TRAVEL_DEST? Unqualified object references (object references that aren’t prefixed with a schema name) are resolved in the following order:

1. A real object with the specified name

2. A private synonym owned by the current user

3. A public synonym

Private synonyms can be useful in a development environment when you have a copy of a table with a different name. A private synonym can be created to refer to the copy of the production table with the same name as the production table. During testing, the developer’s private synonym points to the copy and does not impact the production table. When development is complete, the developer can remove the private synonym and move the new SQL code into a production environment, without changing any table names in the SQL code.