Can We Create a Synonym for View in Oracle?

Synonym for View in Oracle

Yes, you can certainly create a synonym for a view in Oracle Database.

A synonym acts as an alternative name or an alias for a database object. This abstraction layer allows users to refer to an object without needing to know its actual name, its owner, or even its physical location. Synonyms can be created for various database objects, and views are explicitly listed among them.

Naturally, let’s explore the capabilities of Oracle Database’s CREATE VIEW, CREATE SEQUENCE, and CREATE SYNONYM functions, as well as real-world applications including table formation and data insertion.

As a prelude to our examples, we will first construct a simple table and fill it with some data, which will be the basis for illustrating views and sequences.

Similar to spreadsheets with rows and columns, tables are the main way that information is stored in Oracle. Every column in the table has a distinct name and a datatype that is specified. A field is where a row and a column meet, and rows are frequently referred to as records. Column names and their properties are specified by the build TABLE command, which is used to build tables.

Let’s create a Products table to store information about various items:

CREATE TABLE Products (
    ProductID   NUMBER PRIMARY KEY,
    ProductName VARCHAR2(50) NOT NULL,
    Category    VARCHAR2(30),
    Price       NUMBER(10,2)
);

ProductName is a variable-length text that cannot be null, ProductID is a unique numeric identifier and the primary key, Category is a variable-length string, and Price is a numeric number with 10 digits total and 2 decimal places. This statement generates a table called Products with four columns.

Now, let’s insert some sample data into the Products table using the INSERT command:

INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (101, 'Laptop', 'Electronics', 1200.00);
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (102, 'Mouse', 'Electronics', 25.50);
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (103, 'Keyboard', 'Electronics', 75.00);
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (104, 'Desk Chair', 'Furniture', 150.00);
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (105, 'Monitor', 'Electronics', 300.00);
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (106, 'Coffee Table', 'Furniture', 99.99);

After executing these INSERT statements, you’ll typically receive a “1 row created” message for each successful insertion.

To view the contents of the Products table, you can execute a SELECT statement:

SELECT * FROM Products;

Output:

PRODUCTID PRODUCTNAME CATEGORY         PRICE
--------- ----------- ---------------- -------
      101 Laptop      Electronics      1200.00
      102 Mouse       Electronics        25.50
      103 Keyboard    Electronics        75.00
      104 Desk Chair  Furniture         150.00
      105 Monitor     Electronics       300.00
      106 Coffee Table Furniture        99.99

CREATE VIEW

A view in Oracle Database is a virtual table that is based on the set of results from a SQL query. In contrast to standard tables, views don’t actually save data. Rather, they only include a stored SQL query that searches one or more underlying base tables for particular columns and rows. Oracle dynamically runs the defining query for a view when a user queries it, and displays the results as though they were from a table.

Views are important for a few reasons:

Simplifying Complex Queries: Views can conceal the intricacy of sophisticated SQL queries, particularly those that need several joins or aggregations. Views can boost performance by ensuring that business rules are followed by predefining join conditions.
Enforcing Security: By simply revealing a portion of the columns or rows from the underlying tables, views can limit access to sensitive information For example, a view might be made to display only the earnings for a certain division or the salary of a single person. To do this, utilise WHERE clauses, which filter rows according to user-specific criteria or pseudo-columns such as User.
Customising Data Presentation: Views let you show data in a way that is easier for end users or particular applications to comprehend by changing data types, applying functions, or renaming columns using aliases without changing the table structure itself.

Setting Up a View construct VIEW or CREATE OR REPLACE VIEW are the statements used to construct views. By using the OR REPLACE option, you can change an existing view without deleting it first, keeping the grants on the view intact. In most cases, you need the construct VIEW system privilege in order to construct a view.

Let’s create a view named ElectronicsProducts that shows only the electronic items from our Products table:

CREATE OR REPLACE VIEW ElectronicsProducts AS
SELECT ProductID, ProductName, Price
FROM Products
WHERE Category = 'Electronics';

This view extracts a subset of columns and rows from the Products table.

Now, if we query this view, it will execute the underlying SELECT statement:

SELECT * FROM ElectronicsProducts;

Output:

PRODUCTID PRODUCTNAME      PRICE
--------- -----------      -------
      101 Laptop         1200.00
      102 Mouse            25.50
      103 Keyboard         75.00
      105 Monitor          300.00

Any modifications to the information in the underlying Products database will be instantly reflected when you query ElectronicsProducts since views are dynamic.

Views for Security and Complex Queries

Effective tools for putting row-level security into practice are views. Users might be limited to viewing just items that cost more than a specific amount, for instance:

CREATE OR REPLACE VIEW HighValueProducts AS
SELECT ProductID, ProductName, Price
FROM Products
WHERE Price > 100.00;

A custom subset of data is displayed in this view according to a condition.

Additionally, views can make join-related queries simpler. Despite our straightforward example, views can capture intricate join logic between numerous tables, such as departments and employees, in real-world situations.

Considerations for Views

The presence of GROUP BY, DISTINCT, or a reference to the ROWNUM pseudo-column in a view prevents you from directly INSERTING, UPDATE, or DELETING data through the view. DML actions on complicated views, however, can be managed using INSTEAD OF triggers.
If an underlying table is removed, any view that relies on it loses its validity.
Because ORDER BY involves extra sorting, it may result in a performance penalty even though it can be used in a CREATE VIEW command.

CREATE SEQUENCE

Sequences are database objects that are made to automatically produce distinct sequential integers. Frequently utilised to fill primary key values in tables, these unique integers guarantee that every new record has a unique identifier. Transactions and tables have no bearing on the generation of sequences. Accordingly, even if one transaction subsequently rolls back, two users who create a sequence number simultaneously will each receive a distinct value, possibly resulting in “gaps” in the sequence numbers.

Establishing an Order To create sequences, use the CREATE SEQUENCE command. Key choices consist of:

START WITH: The sequence’s initial value.
INCREMENT BY: The value by which the sequence is increased (or decremented, if negative) is known as INCREMENT BY.
NOCACHE: By indicating that values are not pre-allocated in memory, NOCACHE helps to minimise potential gaps but may also have an effect on performance. CACHE defines the pre-allocation rule.
NOCYCLE: Upon reaching its maximum or minimum value, NOCYCLE stops the sequence from resuming.
MAXVALUE/MINVALUE: The top and lower bounds of the sequence are defined by MAXVALUE/MINVALUE.
ORDER/NOORDER: In Oracle Real Application Clusters settings, the ORDER/NOORDER feature ensures that sequence numbers are created in the order of request.

The system privilege CREATE SEQUENCE is required in order to create a sequence.

Let’s produce ProductID numbers for our Products table by creating a series called Product_Seq, which will begin at 107:

CREATE SEQUENCE Product_Seq
START WITH 107
INCREMENT BY 1
NOCACHE
NOCYCLE;

Accessing NEXTVAL and CURRVAL Sequence Values Using pseudo-columns, sequence values are retrieved after creation:

NEXTVAL: The next unique value that becomes available is returned by incrementing the sequence with NEXTVAL. START WITH is the value returned by the initial call to NEXTVAL.
CURRVAL: The most recent value of the sequence produced by NEXTVAL during the current session is returned by the CURRVAL function. In order to use CURRVAL, you must first use NEXTVAL at least once during a session.

Let’s use Product_Seq.NEXTVAL to insert a new product:

INSERT INTO Products (ProductID, ProductName, Category, Price)
VALUES (Product_Seq.NEXTVAL, 'Webcam', 'Electronics', 60.00);

INSERT INTO Products (ProductID, ProductName, Category, Price)
VALUES (Product_Seq.NEXTVAL, 'Office Desk', 'Furniture', 250.00);

If we then query the table:

SELECT * FROM Products;

Output (additional rows):

PRODUCTID PRODUCTNAME      CATEGORY         PRICE
--------- -----------      ---------------- -------
...
      107 Webcam           Electronics        60.00
      108 Office Desk      Furniture         250.00

With Product_Seq, the ProductID values (107 and 108) were automatically produced.

Restrictions on Sequence Usage

CURRVAL and NEXTVAL are not allowed in subqueries, views, SELECT DISTINCT statements, GROUP BY or ORDER BY clauses, or the WHERE clause of a SELECT statement to be utilised. They are also not able to be utilised in CHECK constraints or as a column’s DEFAULT value.

CREATE SYNONYM

A synonym is an alternate name or alias for a database object. Because it adds a layer of abstraction, users can refer to an object without knowing its actual name, owner, or even where it is in the real world. For database applications to achieve location transparency and data independence, this mechanism is essential.

Many database objects, such as tables, views, sequences, procedures, functions, packages, materialised views, Java classes, and user-defined object types, can have synonyms made for them.

Types of Synonyms

Private Synonyms: Only a certain person can access private synonyms, which are made inside their schema.
Public Synonyms: All database users can access public synonyms, which are generated by a privileged user (such a DBA). When a user creates a private object with the same name as a public synonym, the private object will be referenced in subsequent SQL statements.

The statement CREATE SYNONYM or CREATE PUBLIC SYNONYM is used to create synonyms. The construct SYNONYM system privilege is required in order to construct a private synonym in your own schema. In another schema, CREATE ANY SYNONYM is necessary for a private synonym. The system privilege establish PUBLIC SYNONYM is required in order to establish a public synonym.

Let’s create a private synonym called MyProducts for our Products table:

CREATE SYNONYM MyProducts FOR Products;

Now, instead of querying Products, we can use the synonym:

SELECT * FROM MyProducts;

Output (same as SELECT * FROM Products):

PRODUCTID PRODUCTNAME      CATEGORY         PRICE
--------- -----------      ---------------- -------
      101 Laptop         Electronics      1200.00
      102 Mouse            Electronics        25.50
      103 Keyboard         75.00
      104 Desk Chair       Furniture         150.00
      105 Monitor          Electronics       300.00
      106 Coffee Table     Furniture         99.99
      107 Webcam           Electronics        60.00
      108 Office Desk      Furniture         250.00

Benefits of Synonyms

Simplifies Access: Makes Access easier: Users don’t have to identify the schema owner when referring to objects with shorter, more convenient names (e.g., user_schema.object_name).
Data Independence: The synonym definition, not the application code that refers it, needs to be modified whenever the name or location of an underlying object changes. This is known as data independence.
Location Transparency: Through database linkages, synonyms can refer to items in distant databases, allowing for location transparency. This makes dispersed data accessible to apps just like local data would be.

You should keep in mind that synonyms do not replace privileges. For the synonym to be used properly, users still require the proper object privileges (such as SELECT, INSERT, UPDATE, and DELETE) on the underlying database thing.

Ultimately, the core SQL commands CREATE VIEW, CREATE SEQUENCE, and CREATE SYNONYM offer strong data abstraction, automation, and management capabilities in Oracle Database systems, improving the adaptability, security, and maintainability of programs.

Page Content

Tutorials