Sequence & Synonym

Sequence In Oracle

**Its a Numeric Value Generator
**Its Mostly used in Primary Key Columns
**Its One of the Schema Object
**Its a Sharable Object

NEXTVAL::


The Oracle NEXTVAL function is used to retrieve the next value in a sequence. The Oracle NEXTVAL function must be called before calling the CURRVAL function, or an error will be thrown.

Example::

SQL> create sequence Seq_1;

Sequence created.

SQL> select Seq_1.Nextval from dual;

NEXTVAL
----------
1

SQL> select Seq_1.Nextval from dual;

NEXTVAL
----------
2

SQL> select Seq_1.Nextval from dual;

NEXTVAL
----------
3

SQL> create sequence Seq_2
2  START WITH 10
3  INCREMENT BY 3
4  MAXVALUE 10000
5  CYCLE
6  CACHE 5;

Sequence created.

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
10

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
13

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
16

SQL> select Seq_2.nextval from dual;

NEXTVAL
----------
19


CURRVAL::


**It Returns the current value of a sequence.
**If the Currval Once executed the Nextval Should be the Next Value of the Currval.

Example::

SQL> select Seq_2.Nextval from dual;

NEXTVAL
----------
19
SQL> select Seq_2.CURRVAL from dual;

CURRVAL
----------
19

SQL> select Seq_2.Nextval from dual;

NEXTVAL
----------
22


SYNONYM::

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Example::

SQL> CREATE SYNONYM Emp For Employees;

SYNONYM CREATED.

SQL> select * From Emp;

Advantages of synonyms

Synonyms are often used for security and convenience.
for Example, they can do the following things.

• Mask the name and owner of an object.
• Provide location transparency for remote objects of a distributed database.
• Simplify SQL statements for database users

Private & Public Synonyms::

• A normal synonym is called private synonym whereas a public synonym is created by a keyword public.
• A private synonym is accessible within your schema and a public synonym is accessible to any schema in the database.

----------END----------