PL/SQL for Beginners

What Is PL/SQL?

PL SQL basically stands for "Procedural Language extensions to SQL". This is the extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in procedural format. 

It combines the data manipulation power of SQL with the processing power of procedural language to create a super powerful SQL queries. 

It allows the programmers to instruct the compiler 'what to do' through SQL and 'how to do' through its procedural way. 

Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object oriented concepts. 

Architecture of PL/SQL

The PL/SQL architecture mainly consists of following 3 components: 

1. PL/SQL block
2. PL/SQL Engine
3. Database Server

PL/SQL block:

• This is the component which has the actual PL/SQL code.
• This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors)
• It also contains the SQL instruction that used to interact with the database server. 
• All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.
• Following are the different type of PL/SQL units.
• Anonymous Block
• Function
• Library
• Procedure
• Package Body
• Package Specification
• Trigger
• Type
• Type Body

PL/SQL Engine

• PL/SQL engine is the component where the actual processing of the codes takes place.
• PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below).
• The separated PL/SQL units will be handled with the PL/SQL engine itself.
• The SQL part will be sent to database server where the actual interaction with database takes place.
• It can be installed in both database server and in the application server.

Database Server:

• This is the most important component of Pl/SQL unit which stores the data.
• The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server.
• It consists of SQL executor which actually parses the input SQL statements and execute the same. 

Below is the pictorial representation of Architecture of PL/SQL. 

Advantage of Using PL/SQL

1. Better performance, as sql is executed in bulk rather than a single statement
2. High Productivity
3. Tight integration with SQL
4. Full Portability
5. Tight Security
6. Support Object Oriented Programming concepts.

Basic Difference between SQL and PL/SQL

In this section, we will discuss some differences between SQL and PL/SQL 

SQL	PL/SQL
SQL is a single query that is used to perform DML and DDL operations.	PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.
It is declarative, that defines what needs to be done, rather than how things need to be done.	PL/SQL is procedural that defines how the things needs to be done.
Execute as a single statement.	Execute as a whole block.
Mainly used to manipulate data.	Mainly used to create an application.
Interaction with Database server.	No interaction with the database server.
Cannot contain PL/SQL code in it.	It is an extension of SQL, so it can contain SQL inside it.

What is PL/SQL block?

PL/SQL is the procedural approach to SQL in which a direct instruction is given to the PL/SQL engine about how to perform actions like storing/fetching/processing data. These instruction are grouped together called Blocks. 

Blocks contain both PL/SQL as well as SQL instruction. All these instruction will be executed as a whole rather than executing a single instruction at a time. 

Block Structure

PL/SQL blocks have a pre-defined structure in which the code is to be grouped. Below are different sections of PL/SQL blocks 

1. Declaration section
2. Execution section
3. Exception-Handling section

The below picture illustrates the different PL/SQL block and their section order. 

Declaration Section

This is the first section of the PL/SQL blocks. This section is an optional part. This is the section in which the declaration of variables, cursors, exceptions, subprograms, pragma instructions and collections that are needed in the block will be declared. Below are few more characteristics of this part. 

• This particular section is optional and can be skipped if no declarations are needed. 
• This should be the first section in a PL/SQL block, if present.
• This section starts with the keyword 'DECLARE' for triggers and anonymous block. For other subprograms this keyword will not be present, instead the part after the subprogram name definition marks the declaration section.
• This section should be always followed by execution section. 

Execution Section

Execution part is the main and mandatory part which actually executes the code that is written inside it. Since the PL/SQL expects the executable statements from this block this cannot be an empty block, i.e., it should have at least one valid executable code line in it. Below are few more characteristics of this part. 

• This can contain both PL/SQL code and SQL code. 
• This can contain one or many blocks inside it as a nested blocks.
• This section starts with the keyword 'BEGIN'.
• This section should be followed either by 'END' or Exception-Handling section (if present)

Exception-Handling Section:

The exception are unavoidable in the program which occurs at run-time and to handle this Oracle has provided an Exception-handling section in blocks. This section can also contain PL/SQL statements. This is an optional section of the PL/SQL blocks. 

• This is the section where the exception raised in the execution block is handled. 
• This section is the last part of the PL/SQL block. 
• Control from this section can never return to the execution block. 
• This section starts with the keyword 'EXCEPTION'.
• This section should be always followed by the keyword 'END'.

The Keyword 'END' marks the end of PL/SQL block. Below is the syntax of the PL/SQL block structure. 

Note: A block should be always followed by '/' which sends the information to the compiler about the end of the block. 

Types of PL/SQL block

PL/SQL blocks are of mainly two types. 

1. Anonymous blocks
2. Named Blocks

Anonymous blocks:

Anonymous blocks are PL/SQL blocks which do not have any names assigned to them. They need to be created and used in the same session because they will not be stored in the server as a database objects. 

Since they need not to store in the database, they need no compilation steps. They are written and executed directly, and compilation and execution happen in a single process. 

Below are few more characteristics of Anonymous blocks. 

• These blocks don't have any reference name specified for them.
• These blocks start with the keyword 'DECLARE' or 'BEGIN'.
• Since these blocks are not having any reference name, these cannot be stored for later purpose. They shall be created and executed in the same session.
• They can call the other named blocks, but call to anonymous block is not possible as it is not having any reference.
• It can have nested block in it which can be named or anonymous. It can also be nested to any blocks.
• These blocks can have all three sections of the block, in which execution section is mandatory, the other two sections are optional.

Named blocks:

Named blocks are having a specific and unique name for them. They are stored as the database objects in the server. Since they are available as database objects, they can be referred to or used as long as it is present in the server. The compilation process for named blocks happens separately while creating them as a database objects. 

Below are few more characteristics of Named blocks. 

• These blocks can be called from other blocks.
• The block structure is same as an anonymous block, except it will never start with the keyword 'DECLARE'. Instead, it will start with the keyword 'CREATE' which instruct the compiler to create it as a database object.
• These blocks can be nested within other blocks. It can also contain nested blocks.
• Named blocks are basically of two types:

1. Procedure
2. Function

Install Oracle 11g On RHEL 6.5 Using Local Dump

Install Oracle 11g On RHEL 6.5 Using Local Dump

Step 1 Include following package groups during installation of linux.

    Base System > Base
    Base System > Client management tools
    Base System > Compatibility libraries
    Base System > Hardware monitoring utilities
    Base System > Large Systems Performance
    Base System > Network file system client
    Base System > Performance Tools
    Base System > Perl Support
    Servers > Server Platform
    Servers > System administration tools
    Desktops > Desktop
    Desktops > Desktop Platform
    Desktops > Fonts
    Desktops > General Purpose Desktop
    Desktops > Graphical Administration Tools
    Desktops > Input Methods
    Desktops > X Window System
    Development > Additional Development
    Development > Development Tools
    Applications > Internet Browser

Step 2 Download Software

Download Oracle 11gR2 and Unzip Files in one Directory. You should get one directory named “database”

Step 3 Hosts File

        Verify and Ensure you have a Network Interface Card with Static IP Address is configured properly.

#ifconfig

      Verify that the eth0 has a static IP Address. If not, use following set of commands to configure and verify.

#system-config-network-tui

      Use 10.0.2.15 as the IP Address (For Example)

#service network restart

#service NetworkManager restart

#ifconfig

#ping 10.0.2.15

    The “/etc/hosts” file must contain a fully qualified name for the server.

127.0.0.1       localhost.localdomain  localhost

10.0.2.15      oracle.example.com    oracle

Step 4 Set Kernel Parameters

Oracle recommend the following minimum parameter settings.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

The current values can be tested using the following command  /sbin/sysctl -a | grep <param-name>

Step 5 Add or amend the following lines in the “/etc/sysctl.conf” file.

cat>>/etc/sysctl.conf<<EOF
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586
EOF

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Step 6 Add the following lines to the “/etc/security/limits.conf” file.

cat>>/etc/security/limits.conf<<EOF
oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240
EOF

Step 7 Disable secure linux by editing the “/etc/selinux/config” file, making sure the SELINUX flag is set as follows.

SELINUX=disabled

Once the change is complete, restart the server.

Step 8 Install the following packages if they are not already present.

# From Oracle Linux 6.5 DVD
cd /media/cdrom/Server/Packages
yum -y install binutils-2*x86_64*
yum -y install glibc-2*x86_64* nss-softokn-freebl-3*x86_64*
yum -y install glibc-2*i686* nss-softokn-freebl-3*i686*
yum -y install compat-libstdc++-33*x86_64*
yum -y install glibc-common-2*x86_64*
yum -y install glibc-devel-2*x86_64*
yum -y install glibc-devel-2*i686*
yum -y install glibc-headers-2*x86_64*
yum -y install elfutils-libelf-0*x86_64*
yum -y install elfutils-libelf-devel-0*x86_64*
yum -y install gcc-4*x86_64*
yum -y install gcc-c++-4*x86_64*
yum -y install ksh-*x86_64*
yum -y install libaio-0*x86_64*
yum -y install libaio-devel-0*x86_64*
yum -y install libaio-0*i686*
yum -y install libaio-devel-0*i686*
yum -y install libgcc-4*x86_64*
yum -y install libgcc-4*i686*
yum -y install libstdc++-4*x86_64*
yum -y install libstdc++-4*i686*
yum -y install libstdc++-devel-4*x86_64*
yum -y install make-3.81*x86_64*
yum -y install numactl-devel-2*x86_64*
yum -y install sysstat-9*x86_64*
yum -y install compat-libstdc++-33*i686*
yum -y install compat-libcap*
cd /
eject

Note. This will install all the necessary packages for 11.2.0.1. From 11.2.0.2 onwards many of these are unnecessary, but having them present does not cause a problem.

Step 8 Create the new groups and users.

/usr/sbin/groupadd -g 501 oinstall
/usr/sbin/groupadd -g 502 dba
/usr/sbin/groupadd -g 503 oper
/usr/sbin/groupadd -g 504 asmadmin
/usr/sbin/groupadd -g 506 asmdba
/usr/sbin/groupadd -g 505 asmoper
/usr/sbin/useradd -u 502 -g oinstall -G dba,asmdba,oper oracle
/usr/sbin/passwd oracle

Step 9 Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Step 10 Login as root and issue the following command.

xhost +<machine-name>

Step 11 Login as the oracle user and add the following lines at the end of the “.bash_profile” file.

cat>>/home/oracle/.bash_profile<<EOF
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=oracle.example.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Step 12 Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=<machine-name>:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller

Step 13 Post Installation : Edit the “/etc/oratab” file setting the restart flag for each instance to ‘Y’.

cat>>/etc/oratab<<EOF
ORCL:/u01/app/oracle/product/11.2.0/dbhome_1:Y
EOF

Next, create a file called “/etc/init.d/oracledb” as the root user, containing the following.

#!/bin/sh
# chkconfig: 35 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ] then
echo “Oracle startup: cannot start”
exit
fi

case “$1″ in
‘start’)
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “$ORA_HOME/bin/dbstart $ORA_HOME”
su – $ORA_OWNER -c “emctl start dbconsole”
touch /var/lock/subsys/oracledb
;;
‘stop’)
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “$ORA_HOME/bin/dbshut $ORA_HOME”
su – $ORA_OWNER -c “emctl stop dbconsole”
rm -f /var/lock/subsys/oracledb
;;
esac

Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/oracledb

Associate the oracledb service with the appropriate run levels and set it to auto-start using the following command.

chkconfig –add oracledb
---To Increase the size of swap 

[root@localhost ~]# dd if=/dev/zero of=/myswapfile bs=1024M count=4
4+0 records in
4+0 records out
4294967296 bytes (4.3 GB) copied, 6.77915 s, 634 MB/s
[root@localhost ~]# mkswap /myswapfile
Setting up swapspace version 1, size = 4194300 KiB
no label, UUID=9c07ed67-c3d7-48ef-a4a3-9f65794d4d59
[root@localhost ~]# chmod 600 /myswapfile
[root@localhost ~]# swapon /myswapfile
swapon: /myswapfile: swapon failed: Device or resource busy
[root@localhost ~]# vi /etc/fstab
add entry in above file
/myswapfile   swap    swap defaults 0 0

Oracle Merge Statement

Merge Statement

The MERGE statement was introduced in Oracle 9i , For
conditional insert or update data depending on its presence  this is sometimes called as an "upsert". 

In this blog we will learn the syntax with example and performance advantage of the command and error handling in merge command .

To start with ,
we will create two table 
1) A destination table or target table where the rows will be inserted updated or deleted 2) Source table :it can be a table or it can be a from Query joining multiple tables.
---
Semantics
INTO Clause
USING Clause
ON Clause
merge_update_clause
merge_insert_clause
error_logging_clause 

drop table test1;
CREATE TABLE test1 AS SELECT * FROM   all_objects WHERE  1=2;
select * from test1;
--With both clause
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status);
--With not matched clause (insert only)
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status);
--With matched clause (update only)
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN  MATCHED THEN
UPDATE SET a.status = b.status;
-- Both clauses with conditional operation
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status)
WHERE  b.status != 'VALID';
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status)
WHERE  b.status != 'VALID';
-- With matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID';
---Delete clause
CREATE TABLE source AS SELECT level AS id,CASE WHEN MOD(level, 2) = 0 THEN 10
                                               ELSE 20
                                               END AS status,
'Description of level ' || level AS description
FROM dual CONNECT BY level <= 5;
select * from source;
CREATE TABLE destination AS SELECT level AS id,CASE WHEN MOD(level, 2) = 0 THEN 10
                                                    ELSE 20
                                                    END AS status,
'Description of level ' || level AS description
FROM   dual CONNECT BY level <= 10;
select * from destination;

MERGE INTO destination d
USING source s
ON (s.id = d.id)
WHEN MATCHED THEN
UPDATE SET d.description = 'Updated'
DELETE WHERE d.status = 10;

RMAN Recovery Manager

RMAN

Rman is a client program provided by oracle ,This utility stands for R -recovery MAN-manager 

Rman is a client program which work as SQL*PLUS utlity, RMAN will be available in the bin directory where the oracle will be installed

eg: <C:\Oracle\app\product\11.1.0\db_1\bin> rman.exe this will be a executable program

To run this utility

We have first set the oracle SID ,

Why we need to connect to oracle SID?

The reason is to tell rman which database we need to connect to So that we can take whole backup 

command to connect 

prompt>set ORACLE_SID=MYDATABASE

prompt>echo %ORACLE_SID%

Prompt> rman taget / (we dont use sys as sysdba here it implicit)

What about the above command?

Above command is saying is connect to the database for which SID is set to (The database which we are targeting or restoring is known as target database)

when ever you connect to rman it connect as sysdba user is implicit here we dont need that 

----

connected to rman DBID<10 digit number>

---

RMAN> backup database;

===

it will start taking up the backup

===

Rman connects to the control files and control files will tell them where the datafiles are .

Oracle strongly recommends that you do not make inconsistent, closed database backups in NOARCHIVELOG mode. If such a backup is used to restore the database, then data corruption might result. 

If you run the database in ARCHIVELOG mode, then you do not have to back up the whole database at one time

A backup of online datafiles is called an online backup. This requires that you run your database in ARCHIVELOG mode. 

The backup set will have the backup pieces the backup pecies will have the 8 character file which will be named by rman , 

We can spit the database into multiple pecies 

To check the list of the backup set we can which with the 

RMAN> list backup ;

it will write the backup in the database folder.It will be wrritten in the binary format which rman can read them 

---

DELETE the backup 

We can use the delete comand

RMAN> delete backup;

it will delete from the disk as well 

--------------------

NOW take a backup with the Archive logs 

We need a Archive logs backup when the database is runnning to macke backup consistent and to take care of the fractured blocks

RMAN> backup Database archivelogs;

It will switch the and takes the backup of the archive logs 

when we restore the backup our it will take care of the 

---------------

A whole database backup is either a consistent backup or an inconsistent backup. Whether a backup is consistent determines whether you need to apply redo logs after restoring the backup.

A datafile backup is a backup of a single datafile. Datafile backups, which are not as common as tablespace backups, are valid in ARCHIVELOG databases. The only time a datafile backup is valid for a database in NOARCHIVELOG mode is if: