SEQUENCES

A sequence is used to generate numbers in sequence. You can use sequences to insert unique values in Primary Key and Unique Key columns of tables. To create a sequence give the CREATE SEQUENCE statement.

CREATING SEQUENCES

create sequence bills

     start with 1

     increment by 1

     minvalue 1

     maxvalue 100/nomaxvalue

     cycle/nocycle

     cache 10/nocache;

The above statement creates a sequence bills it will start with 1 and increment by 1. It’s maxvalue is 100 i.e. after 100 numbers are generated it will stop if you say NOCYCLE, otherwise if you mention cycle then again it will start with no. 1. You can also specify NOMAXVALUE in that case the sequence will generate infinite numbers.

The CACHE option is used to cache sequence numbers in System Global Area (SGA). If you say CACHE 10 then oracle will cache next 10 numbers in SGA. If you access a sequence number then oracle will first try to get the number from cache, if it is not found then it reads the next number from disk. Since reading the disk is time consuming rather than reading from SGA it is always recommended to cache sequence numbers in SGA. If you say NOCACHE then Oracle will not cache any numbers in SGA and every time you access the sequence number it reads the number from disk.

Accessing Sequence Numbers.

To generate Sequence Numbers you can use NEXTVAL and CURRVAL for example to get the next sequence number of bills sequence type the following command.

Select bills.nextval from dual;

BILLS

-----

1

NEXTVAL gives the next number in sequence. Whereas,  CURRVAL returns the current number of the sequence. This is very handy in situations where you have insert records in Master Detail tables. For example to insert a record in SALES master table and SALES_DETAILS detail table.

insert into sales (billno,custname,amt)

  values (bills.nextval,’Sami’,2300);

insert into sales_details (billno,itemname,qty,rate) values

     (bills.currval,’Onida’,10,13400);

ALTERING SEQUENCES

To alter sequences use ALTER SEQUENCE statement. For example to alter the bill sequence MAXVALUE give the following command.

ALTER SEQUENCE BILLS

     MAXVALUE 200;

Except Starting Value, you can alter any other parameter of a sequence. To change START WITH parameter you have to drop and recreate the sequence.

DROPPING SEQUENCES

To drop sequences use DROP SEQUENCE command. For example to drop bills sequence give the following statement

drop sequence bills;

Listing Information About Sequences

To see how many sequences are there in your schema and what are there settings give the following command.

select * from user_sequences;

SYNONYMS

A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package.

There are two types to SYNONYMS they are

            PUBLIC SYNONYM

            PRIVATE SYNONYM

If you a create a synonym as public then it can be accessed by any other user with qualifying the synonym name i.e. the user doesn’t have to mention the owner name while accessing the synonym. Nevertheless the other user should have proper privilege to access the synonym. Private synonyms needs to be qualified with owner names.

CREATING SYNONYMS

To create a synonym for SCOTT emp table give the following command.

Create synonym employee for scott.emp;

A synonym can be referenced in a DML statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named EMPLOYEE refers to a table or view, then the following statement is valid:

select * from employee;

Suppose you have created a function known as TODAY which returns the current date and time. Now you have granted execute permission on it to every other user of the database. Now these users can execute this function but when the call they have to give the following command:

select scott.today from dual;

Now if you create a public synonym on it then other users don’t have to qualify the function name with owner’s name. To define a public synonym give the following command.

create public synonym today for scott.today;

Now the other users can simply type the following command to access the function.

select today from dual;

Dropping Synonyms

To drop a synonym use the DROP SYNONYM statement. For example, to drop EMPLOYEE synonym give the statement

drop synonym employee;

Listing information about synonyms

To see synonyms information give the following statement.

select * from user_synonyms;