Exception Handling Program in PL/SQL Oracle

It is a sample PL/SQL Oracle exception handling program to handle errors. It is used for error tolerance and error avoidance.
In Pl/SQL exception is used to handle run time errors. When a run time error occurs then it moves to exception handler block in PL/SQL program.

Aim -PL/SQL program to perform exception handling.

Program: -

declare
  empn emp.empno%type;
  row_type1 emp%rowtype;
begin
  empn:=&empn;
  select * into row_type1 from emp where empno=empn;
  dbms_output.put_line('Name: '||row_type1.ename);
  exception when NO_DATA_FOUND then
  dbms_output.put_line('No such employee');
end;

Output: -

Enter value for empn: 7566
old 5: empn:=&empn;
new 5: empn:=7566;
Name: JONES

Enter value for empn: 1001 /* Such employee number(empno) is not in emp table */
old 5: empn:=&empn;
new 5: empn:=1001;
No such employee

Check Number is Armstrong in PL/SQL Programming

It is a sample pl/sql program to take input a number from user and it checks accepted number is Armstrong or not.

Aim - PL/SQL program to check whether number is Armstrong or not.

Program:-

declare
  pnum number(5);
  tot number(5);
  lp number(3);
  tmp number(5);
begin
  pnum:=&pnum;
  tmp:=pnum;
  tot:=0;
  while tmp>0
  loop
    lp:=tmp mod 10;
    tot:= tot + (lp*lp*lp);
    tmp:=floor(tmp/10);
  end loop;
  if(tot like pnum) then
    dbms_output.put_line(pnum||' is armstrong.');
  else
    dbms_output.put_line(pnum||' is not armstrong.');
  end if;
end;

Output: -
Enter value for pnum: 153
old 7: pnum:=&pnum;
new 7: pnum:=153;
153 is armstrong.

PL/SQL Example to find average of three numbers

This is a PL/SQL sample example to accept three numbers from the user and find average of accepted numbers.

Aim - PL/SQL Example to find average of three numbers

Sample Program : -

declare
  a number(3);
  b number(3);
  c number(3);
  avgr number(5,2);
begin
  a:=&a;
  b:=&b;
  c:=&c;
  avgr:=((a+b+c)/3);
dbms_output.put_line('Average of a, b, c is: '||avgr);
end;


Output of PL SQL Block: -

Enter value for a: 22
old   7:   a:=&a;
new   7:   a:=22;
Enter value for b: 32
old   8:   b:=&b;
new   8:   b:=32;
Enter value for c: 43
old   9:   c:=&c;
new   9:   c:=43;
Average of a, b, c is: 32.33

PL/SQL program to generate Fibonacci series

It is a sample example of generate fibonacci series upto 10 numbers.

Aim - PL/SQL program to generate Fibonacci series.

PLSQL Sample Program: -

declare
  f1 number(3);
  f2 number(3);
  f3 number(3);
  num number(3);
begin
  f1:=0;
  f2:=1;
  f3:=0;
  num:=1;
  while num<=10
  loop
 dbms_output.put_line(f3);
 f1 :=f2;
 f2:=f3;
 f3:=f1+f2;
 num:=num+1;
  end loop;
end;

Output of PL SQL Block: -
0
1
1
2
3
5
8
13
21
34

PL/SQL Program to reverse a string

This is a sample PLSQL example to accept a string from user and print reverse of accept string.

Aim - PL/SQL Program to reverse a string.

Sample PLSQL Program: -

declare
  str1 varchar2(30);
  len number(3);
  str2 varchar2(30);
  i number(3);
begin
  str1:='&str1';
  len:=length(str1);
  for i in reverse 1..len
   loop
    str2:=str2 || substr(str1,i,1);
   end loop;
  dbms_output.put_line('Reverse string is: '||str2);
end;




Output of PL SQL Block: -
Enter value for str1: hello
old   6:   str1:='&str1';
new   6:   str1:='hello';
Reverse string is: olleh