Oracle Objects

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

ORACLE PROCEDURE-

A group of PL/SQL statements is called a procedure and what makes it unique is that it can be called by name. The call spec or the call specification is utilized to call a Java method or a third-generation language routine from SQL and PL/SQL, and to serve this purpose it specifies their existence.

IN: Being a default parameter, it is used to pass a value to the subprogram.

OUT: When specified, it is used to return a value to the caller.

IN OUT: When specified, it is used to get an updated value to the caller by passing an initial value to the subprogram.

 

Syntax-

CREATE [OR REPLACE] PROCEDURE procedure_name 

    [ (parameter [,parameter]) ] 

IS 

    [declaration_section] 

BEGIN 

    executable_section 

[EXCEPTION 

    exception_section] 

END [procedure_name];  

Oracle Function-

A function is a subprogram that is used to return a single value. You must declare and define a function before invoking it. It can be declared and defined at a same time or can be declared first and defined later in the same block.

 

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;

Oracle Trigger-

In Oracle, you can define procedures that are implicitly executed when an INSERT, UPDATE or DELETE statement is issued against the associated table. These procedures are called database triggers.

 

DDL Triggers-

The Data Definition Language (DDL) command events such as Create table, create view, drop table, drop view, and Alter table cause the DDL triggers to be activated.

 

DML Triggers-

The Data uses manipulation Language (DML) command events that begin with Insert, Update, and Delete set off the DML triggers. corresponding to insert table, update view, and delete table.

 

Syntax-

CREATE OR REPLACE TRIGGER trigger name

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

ON table_name

[FOR EACH ROW]

BEGIN

  -- Trigger logic goes here

END;

/

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;

 

 

CYCLE-

The cycle option repeats the same set of sequence values once it reaches the max value. The next value will be reset to minimum value once it reaches the max value.

NOCYCLE-

The oracle sequence by default creates with no cycle option. The no cycle option will give you an error message when you try to fetch next value after reaching the max value. No cycle option will not generate more values once it reaches max value.

Table-

A table is a type of database object in Oracle Database 19c that is used to store and arrange structured data. This is an essential part of the relational database management system (RDBMS) Oracle. Tables are useful for a variety of applications, including the storage of financial data, customer information, product catalogues, and more. They store data in a tabular format with rows and columns.

Syntax-

 

CREATE TABLE your_table_name (

    column1_name data_type,

    column2_name data_type,

    column3_name data_type,

    -- Add more columns as needed

    constraint_name CONSTRAINT constraint_type,

    -- Add constraints as needed

);

Here's a breakdown of the components in the SQL statement: 

CREATE TABLE-

This is the SQL command used to create a new table.

your_table_name-

Replace this with the name you want to give to your table.

(column1_name data_type, column2_name data_type, ...)-

This part defines the columns of your table. You specify the column name and its data type. For example, column1_name is the name of the first column, and data_type should be replaced with the appropriate data type for that column (e.g., VARCHAR2, NUMBER, DATE, etc.). You can define as many columns as needed, each separated by a comma.

(constraint name CONSTRAINT constraint type)-

You can define constraints for your table. Constraints are rules that enforce data integrity. For example, you can define a primary key constraint to ensure the uniqueness of values in a particular column. constraint name is the name of the constraint, and constraint type specifies the type of constraint (e.g., PRIMARY KEY, FOREIGN KEY, CHECK, etc.). Constraints are optional, but they are often used to ensure data consistency and accuracy.

View-

A view is a database object in Oracle Database 19c that shows the outcome of a query on one or more tables or views. Since a view doesn't hold data on its own and instead offers a structured method of accessing and displaying data from other tables, it can be compared to a virtual table. Views are useful for a number of things, such as encapsulating complex queries, facilitating security, and making data access easier.

Syntax-

 

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name

AS

SELECT column1, column2, ...

FROM table_name

[WHERE condition];

 

Let's break down the components of the CREATE VIEW statement:

CREATE VIEW-

This is the SQL statement used to create a view.

OR REPLACE (optional)-

If specified, it allows you to modify an existing view with the same name.

FORCE | NOFORCE (optional)-

Specifies whether to create the view even if the base table(s) do not exist or are inaccessible (FORCE), or to create the view only if the base table(s) exist (NOFORCE).

view name-

Replace this with the name you want to give to your view.

AS-

This keyword is used to specify that you are defining the view based on the result of a query.

SELECT column1, column2, ...: -

Here, you define the columns you want in the view. You can select columns from one or more tables or other views.

FROM table name-

 This is where you specify the table(s) or view(s) from which the data will be retrieved for the view.

WHERE condition (optional)-

You can add a WHERE clause to filter the data in the view.

 

 

Materialized View –

A materialised view is a database object in Oracle Database 19c that keeps the output of a query as a physically stored table. Materialised views, as opposed to conventional (non-materialized) views, are more efficient at performing queries because they compute and store the answers to difficult or expensive queries in advance. When real-time data is not needed, materialised views are frequently used for data storage, reporting, and enhancing query response times.

Syntax-

CREATE MATERIALIZED VIEW view_name

BUILD [IMMEDIATE | DEFERRED] REFRESH [FAST | COMPLETE | FORCE] ON [COMMIT | DEMAND | LAST {expression} | NEXT {expression} | WITH {OUT} SCHEMA ONLY]

AS

SELECT columns

FROM tables

[WHERE condition];

Let's break down the components of the CREATE MATERIALIZED VIEW statement:

CREATE MATERIALIZED VIEW-

This is the SQL statement used to create a materialized view.

view name-

Replace this with the name you want to give to your materialized view.

BUILD [IMMEDIATE | DEFERRED]-

Specifies whether the materialized view should be populated immediately (IMMEDIATE) or at a later time (DEFERRED).

REFRESH [FAST | COMPLETE | FORCE] ON [COMMIT | DEMAND | LAST {expression} | NEXT {expression} | WITH {OUT} SCHEMA ONLY]-

Specifies when and how the materialized view should be refreshed. You can choose from different refresh methods and scheduling options based on your requirements.

AS SELECT columns FROM tables [WHERE condition]-

This is where you define the query that specifies the data to be stored in the materialized view. You can retrieve data from one or more tables and apply filtering conditions if necessary.

Create the materialized view log-

After creating the table in the new user/schema, you can create the materialized view log in the same schema.

CREATE MATERIALIZED VIEW LOG ON employees;

Create the materialized view-

You can proceed to create the materialized view using the new user/schema.

 

-- Create the materialized view in the new user's schema

CREATE MATERIALIZED VIEW A2.employee_salary_summary

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

AS

SELECT employee_id, SUM(salary) as total_salary

FROM A2.employees

GROUP BY employee_id;

 

Package-

Packages can be created in Oracle Database 19c, just as they could in earlier versions. Packages are database objects that let you bring together similar PL/SQL procedures, functions, variables, and cursors into a single, reusable unit. This makes managing and maintaining your PL/SQL code easier.

Syntax-

CREATE OR REPLACE PACKAGE BODY my_package AS

  -- Implement the procedures and functions here

  PROCEDURE my_procedure(param1 NUMBER) IS

  BEGIN

    -- Procedure implementation

    NULL;

  END my_procedure;

 

  FUNCTION my_function(param2 NUMBER) RETURN NUMBER IS

  BEGIN

    -- Function implementation

    RETURN param2 * 2;

  END my_function;

END my_package;

/

Synonym – A Synonym is a simple alias for a table, view, sequence or other database object. Because a synonym is just an alternative name for an object so it requires no storage space.

Synonyms provide a level of security by hiding the name and owner of a schema object such as a table or a view. On top of that, they provide location transparency for remote objects of a distributed database.

Synonyms create a level of abstraction of the underlying schema objects so that you can rename and move of the underlying objects without affecting the applications based on the synonyms. Note that synonyms themselves are not secured. When you grant object privileges on a synonym, you are granting privileges on the underlying object, and the synonym only acts as an alias in the GRANT statement.

Synonyms can be public or private. A public synonym is accessible to every user in a database and owned by a specified group named PUBLIC while a private synonym is stored a specific schema owned by a specific user and available only to that user.

Create synonym – show you how to create a new synonym for a table.

Drop a synonym – describe how to drop a synonym from the database.

In a distributed database system, synonyms help simplify SQL statements.

Suppose you have a table called sales in the schema owned by the user lion, and you granted the SELECT privilege for the sales table to PUBLIC.

To query data from the sales table, you use the following statement:

SELECT * FROM HARI.PRODUCTS;

Notice that you must include the name of the schema and the table name to in the query.

To simplify this query, you can create a public synonym using the following CREATE PUBLIC SYNONYM statement:

CREATE PUBLIC SYNONYM hari_products FOR HARI.PRODUCTS;

Now, you can query the table sales with a simpler SQL statement:

SELECT * FROM hari_products;

                            

Type – A data type is a category that defines the type of values that a column can hold. In a database, a data type is a specification that determines the type of data that can be stored in a column of a table. This ensures that only the appropriate data type can be stored in the column.

 Why are Data types important?

Data types are critical in any database management system as they ensure the accuracy of the data stored in the database and efficiency working with that data. Specifying the type of data that can be stored in a column helps prevent errors, save space, and improve performance. Properly choosing a data type makes it easier to understand and manage the data in our database instance.

 Datatypes in Oracle

Oracle, like many relational databases, offers a wide range of datatypes to store different kinds of data. To ensure the accuracy and efficiency of your queries, it is important to have a good understanding of the different datatypes in Oracle and to select the most suitable one for each column. The datatypes offered by Oracle are as follows:

·        Numerical Datatypes

Oracle offers several numerical datatypes, including NUMBER, INTEGER, and FLOAT. NUMBER is used to store numeric values with high precision, INTEGER is used to store whole numbers, and FLOAT is used to store approximate numeric values.

They are categorized into two types.

1.                  Int

2.                  Number(P, S)

Storing integer format values only.

Sno int —————–> Sno Number(38)

Note: When we use INT datatype on the column at the time of table creation then internally oracle server will convert it into “number” datatype with a maximum size is 38 digits.

 ·        Character Datatypes

Oracle has 8 character datatypes CHAR, NCHAR, VARCHAR, VARHAR2, and NVARHAR2, CLOB, NCLOB, and LONG. CHAR and VARCHAR2 are more important. VARCHAR2 is used to store variable-length character strings, while CHAR is used to store fixed-length character strings.

 ·        Date And Time Datatypes

 

Oracle provides two datatypes for date and time information: DATE and TIMESTAMP. DATE is used to store date and time information, and TIMESTAMP is used to store more precise date and time information.

·        Large Object Datatypes

Oracle has two datatypes for large objects, BLOB and CLOB. BLOB is used to store binary data, while CLOB is used to store character data.

BLOB stands for Binary Large Object and this Data Type is used for storing image/audio /video files in the form 010010101001 binary format. The maximum size is 128 TB.

CLOB stands for character large object and this Data Type is used for storing non-Unicode characters. The maximum size is 128 TB.

DB Link – A database link is a connection from the Oracle database to another remote database. The remote database can be an Oracle Database or any ODBC-compliant database such as SQL Server or MySQL.

Ø Why do you need a database link

A database link allows a user or program to access database objects such as tables and views from another database.

Once you create a database link, you can access the tables or views from the remote database using the following pattern:

table_name@database_link

For example, you can query data from a table in the remote database as if it were in the local server:

SELECT * FROM remote_table@database_link;

When accessing a remote table or view over the database link, the Oracle database acts as an Oracle client.

Using a synonym to simplify the syntax for accessing objects via a database link

To simplify the syntax, you can create a synonym for the remote object accessed via the database link and use this object as if it were a local object.

This syntax shows how to create a synonym for a remote table:

CREATE SYNONYM local_table

FOR remote_table@database_link;

This query uses the synonym instead of the remote table name with the database link:

SELECT * FROM local_table;

Oracle CREATE DATABASE LINK statement

There are two types of database links: public and private.

Private database links are visible to the owners while public database links are visible to all users in the database. For this reason,  public database links may pose some potential security risks.

To create a private database link, you use the CREATE DATABASE LINK statement as follows:

 CREATE DATABASE LINK db1link

CONNECT TO remote_user IDENTIFIED BY password

USING 'remote_database';

In this syntax:

• First, specify the name of the database link after the CREATE DATABASE LINK keywords.
• Second, provide the user and password of the remote database after the CONNECT TO and IDENTIFIED BY keywords.
• Finally, specify the service name of the remote database. If you specify only the database name, Oracle will append the database domain to the connect string to form a complete service name.

Typically, you add an entry in the tnsnames.ora file and reference it as the remote_database in the USING clause.

The following statement shows how to create the private database link to a user in a remote database with a full connection string.

CREATE DATABASE LINK dblink

    CONNECT TO remote_user IDENTIFIED BY password

    USING '(DESCRIPTION=

                (ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))

                (CONNECT_DATA=(SERVICE_NAME=service_name))

            )';

To create a public database link, just add the PUBLIC keyword:

CREATE PUBLIC DATABASE LINK dblink

    CONNECT TO remote_user IDENTIFIED BY password

    USING 'remote_database';

Create a database link example

In this example, we will create a database link to a remote Oracle Database server located on the server 10.50.100.143 with the port 1521 and service name SALES.

First, add the following entry to tnsnames.ora file in the local Oracle Database server. Typically, the tnsnames.ora is located in the directory /NETWORK/ADMIN/ under ORACLE_HOME:

SALES =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.100.143)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = SALES_PRD)

    )

)

Next, use the CREATE DATABASE LINK statement to create a new private database link that connects to the SALES database via bob‘s account:

CREATE DATABASE LINK sales

    CONNECT TO bob IDENTIFIED BY Abcd1234

    USING 'SALES';

Then, issue the SELECT statement to query data from the customers table on the SALES database:

SELECT * FROM customers@sales;

Here is the output:

After that, insert a new row into the customers table:

INSERT INTO customers@sales(customer_id, name, email)

VALUES (2,'XYZ Inc','contact@xyzinc.com');

Finally, query data from the customers table again:

SELECT * FROM customers@sales

The result set is as follows:

By Tarif Ahmed