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

PL/SQL Procedure Sample Example

It is a sample PL/SQL Oracle example using procedure.
When create a procedure IN and OUT keyword used during pass parameters. IN is used to pass values to the procedure and OUT is used to return values to the caller.

Aim - PL/SQL Oracle program to add two numbers using procedure.

Procedure: -
create or replace procedure add_proc(n1 IN number,n2 IN number, tot OUT number)as
begin
tot:=n1+n2;
end;

Sample Program Code: -

declare
 num1 number(3);
 num2 number(3);
 result number(4);
begin
 num1:=&num1;
 num2:=&num2;
 add_proc(num1,num2,result);
 dbms_output.put_line('Addition is: '||result);
end;

First execute only the PL/SQL procedure part then fire program. In this program first execute the add_proc procedure than execute the PL/SQL program.


Output: -
When Execute Procedure then we get output like:
Procedure created.


When Execute Program then:
Enter value for num1:
old 6: num1:=&num1;
new 6: num1:=23;
Enter value for num2: 34
old 7: num2:=&num2;
new 7: num2:=34;
Addition is: 57

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.

Reverse a Number in PL/SQL Programming

It is a sample PL/SQL program to take input a number form user and print reverse of accepted number.

Aim - PL/SQL Program to accept a number from user and print number in reverse order.

Program: -

declare
  num1 number(5);
  num2 number(5); 
  rev number(5);
begin
  num1:=&num1;
  rev:=0;
  while num1>0
  loop
    num2:=num1 mod 10;
    rev:=num2+(rev*10);
    num1:=floor(num1/10);
  end loop;
  dbms_output.put_line('Reverse number is: '||rev);
end;

Output: -
Enter value for num1: 12345
old 8: num1:=&num1;
new 8: num1:=12345;
Reverse number is: 54321