Different Types of Objects in Oracle Database

Hey guys, in this blog I am going to explain to you about Different types of objects in Oracle Database.

Different Types of Objects in Oracle Database-

Different kinds of objects are used to store and manage data in an Oracle database. These objects fulfil various functions and are arranged within the database to allow for effective data storage and retrieval. Here are some of the most popular types of Oracle Database objects:

• Table
• View
• Materialized View
• Package
• Procedure
• Sequence
• Trigger
• Synonym
• Function
• Type
• DB Link
• Index
• OLTP (Online Transaction Processing)
• Data Warehouse

Table-

A table is the most basic database object, storing organized data in rows and columns. It is a representation of entities and their properties. Tables are the heart of a relational database, where data is stored, retrieved, and managed.

Syntax-

CREATE TABLE employees (

    employee_id NUMBER,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    hire_date DATE,

    salary NUMBER(10, 2)

);

View-

A view is a virtual table that allows you to present data from one or more tables in a unique manner. Views do not save data and instead function as saved queries. They can help to simplify complex searches while also providing some security by restricting access to underlying data.

Syntax-

CREATE VIEW A1.employee_view AS

SELECT employee_id, first_name, last_name, hire_date, salary

FROM employees;

Materialized View-

A materialized view is a stored replica of query results. Materialized views, as opposed to normal views, store data. They can be renewed on a regular basis and are used to improve query performance, particularly for complicated and frequently requested data.

Syntax-

CREATE MATERIALIZED VIEW view_name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE]

ON [COMMIT | DEMAND]

AS

SELECT column1, column2, ...

FROM source_table

WHERE conditions;

Package-

A package is a database item that puts together related procedures, functions, and variables. Packages aid in the organization and encapsulation of code for improved manageability and reusability.

Syntax-

CREATE OR REPLACE PACKAGE package_name AS

  -- Declare procedures, functions, variables, and types here

  PROCEDURE procedure_name;

  FUNCTION function_name RETURN data_type;

END package_name;

/

Procedure-

A procedure is a database item that consists of a collection of SQL or PL/SQL statements. Procedures are commonly used in databases to perform operations or alter data. They might be called upon to carry out certain tasks.

Syntax-

CREATE OR REPLACE PROCEDURE add_employee(

  emp_id NUMBER,

  emp_name VARCHAR2,

  emp_salary NUMBER

) IS

BEGIN

  INSERT INTO employees (employee_id, first_name, salary)

  VALUES (emp_id, emp_name, emp_salary);

  COMMIT; -- Commit the transaction

END add_employee;

/

Sequence-

A sequence is a database item that generates unique numbers or identifiers. Sequences are frequently employed to generate primary keys or to maintain table order.

Syntax-

CREATE SEQUENCE sequence_name

  START WITH initial_value

  INCREMENT BY increment_value

  MINVALUE min_value

  MAXVALUE max_value

  CYCLE|NOCYCLE

  CACHE cache_size;

Trigger-

A trigger is a piece of PL/SQL code that is executed automatically in response to specified database events, such as table insert, update, or delete actions. Triggers are used to enforce data integrity standards and to carry out auditing duties.

Syntax-

CREATE OR REPLACE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE}

ON table_name

[FOR EACH ROW]

BEGIN

  -- Trigger logic goes here

END;

/

Synonym-

A synonym is a database object alias, such as a table, view, or method. Synonyms make it easier to refer to items and can add an extra layer of protection by restricting access to the underlying objects.

Syntax-

CREATE [PUBLIC] SYNONYM synonym_name

FOR object_name[@db_link];

Function-

A database object containing a collection of SQL or PL/SQL commands that return a value is known as a function. Functions can be used within SQL statements to perform computations or data retrieving.

Syntax-

CREATE OR REPLACE FUNCTION function_name (parameter1 datatype, parameter2 datatype, ...)

RETURN return_datatype IS

BEGIN

  -- Function logic goes here

  -- You can use the parameters to perform operations

  RETURN result_value;

END function_name;

Type-

A user-defined type (UDT) in Oracle refers to bespoke data types developed by users. These data types can be utilized as table attributes or as parameters in procedures and functions.

Syntax-

Create an Object Type Specification:

CREATE OR REPLACE TYPE type_name AS OBJECT (

  attribute1 datatype,

  attribute2 datatype,

  -- Define more attributes as needed

);

DB Link (Database Link)-

A database link is a connection to another Oracle database that allows data in the remote database to be accessed or changed. It is used for database operations that are distributed.

Index-

An index is a database object that speeds up data retrieval by allowing faster access to data. Indexes are collections of sorted key values and references to relevant rows in a table.

Syntax-

CREATE INDEX index_name

ON table_name (column1, column2, ...);

OLTP (Online Transaction Processing)-

An OLTP database system is one that is designed to manage and process large volumes of short, fast, and often parallel transactions. It is best suited for inserting, modifying, and retrieving individual records.

Data Warehouse-

A data warehouse is a specialized database that collects, stores, and analyses massive amounts of historical data. To enable business intelligence, data warehouses are optimized for complicated queries, reporting, and data analysis.