Oracle Forms Exception Handling: NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS

Oracle Forms Exception Handling: NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS

EXCEPTION block in PLSQL Oracle Forms is used to track the exceptions. Following is the PLSQL code snippet which uses NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS exceptions. If the SQL SELECT query does not return any data, NO_DATA_FOUND exception is fired. If the SQL SELECT query returns more than one row where it was expected to return only one row, TOO_MANY_ROWS exception can be used to track this kind of exception. If you are not sure what kind of exception can the code throw, use OTHERS exception.

DECLARE
  DEPARTMENT_NAME VARCHAR(60);
BEGIN
  SELECT DEPTNAME INTO DEPARTMENT_NAME FROM DEPT WHERE DEPTNO = 20;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    MESSAGE('No data found');
  WHEN TOO_MANY_ROWS THEN
    MESSAGE('More than one row found');
  WHEN OTHERS THEN
    NULL; -- don't do anything and just return from the procedure
END;

Difference between WHEN-VALIDATE-ITEM and KEY-NEXT-ITEM triggers

Difference between WHEN-VALIDATE-ITEM and KEY-NEXT-ITEM triggers

WHEN-VALIDATE-ITEM and KEY-NEXT-ITEM triggers are very close to each other and create a lot of confusion. Following are three differences between them to clear the picture a little bit.

1. Whenever the user changes the value in the item and tries to move out of that item using ENTER or TAB or MOUSE, WHEN-VALIDATE-ITEM trigger is fired. But, in case of KEY-NEXT-ITEM trigger, if user moves out using MOUSE, it will not fire. So, the validation written on this trigger will not fire. Better use, WHEN-VALIDATE-ITEM trigger in this case as it also works with MOUSE.

2. KEY-NEXT-ITEM trigger fires before the WHEN-VALIDATE-ITEM trigger.

3. KEY-NEXT-ITEM trigger will fire every time you move to the next field from that field but WHEN-VALIDATE-ITEM will fire only when you have acutally made any changes to that item. If you have made no changes in the item, it will not fire when you move out this item.

Personally, I prefer to use WHEN-VALIDATE-ITEM trigger in many situations.

Oracle Forms Tutorials: WHEN-VALIDATE-ITEM trigger

Oracle Forms Tutorials: WHEN-VALIDATE-ITEM trigger

Consider that you have an oracle form on which there is a datablock which uses EMP table. EMP table has a column called SALARY. Now there is a contraint on the SALARY column that it should be greater than or equal to $1000. Your requirement is that whenever any user fills salary in the Oracle Forms ITEM (say ITEM_SALARY) and tabs out from that item, a validation should fire and if the value filled is not valid, it should give you an error message and does not let the cursor go to the other item. In these situations WHEN-VALIDATE-ITEM trigger is used. Following is the PLSQL code you should write on the WHEN-VALIDATE-ITEM trigger of the ITEM_SALARY item.

IF :ITEM_SALARY < 1000 THEN

    MESSAGE('ERROR: Salary must be at least $1000 or more.');

    RAISE FORM_TRIGGER_FAILURE; -- To keep the cursor in the item

END IF;

You should also go through this video on YOUTUBE by Edward Honour.

In this video, he tries to pick the department name when the user enters the department number. If department number does not exist in the database, he shows the error message and does not let the cursor to go to the other item using FORM_TRIGGER_FAILURE trigger. He has used NO_DATA_FOUND exception for showing the error message. Following is the code used in this video:

BEGIN

SELECT DEPT_NAME INTO :BLOCKNAME.ITEMNAME FROM DEPT WHERE        DEPT_NO = :BLOCKNAME.ITEMNAME2;

EXCEPTION

WHEN NO_DATA_FOUND THEN

MESSAGE('Invalid Department Number');

RAISE FORM_TRIGGER_FAILURE;

END

3 Simple and Interesting PLSQL Programs to Explain Triggers

Triggers in PL/SQL are the blocks which are automatically fired before or after any alteration (insert, update, delete) is done to the table. Here are 3 simple programs which will illustrate the concept of triggers very efficiently. Have a look...

Consider the following table named student. It contains roll no, name and marks of each student.

roll          name     marks
20034   SID        69
20035   HARRY 88
20036   TANK    34

1. Program to illustrate the use of BEFORE TRIGGER. This trigger will always enter the name of student in capital letters.

CREATE OR REPLACE TRIGGER capital_name BEFORE INSERT OR UPDATE ON student FOR EACH ROW
BEGIN
:NEW.name := UPPER(:NEW.name);
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

Then instead of ‘Steven’, ‘STEVEN’ is inserted into the table.

2. Program to illustrate the use of BEFORE TRIGGER. This trigger will not allow to do any action with student table on a specified day, here saturday.

CREATE OR REPLACE TRIGGER no_action BEFORE INSERT OR UPDATE OR DELETE ON student FOR EACH ROW
BEGIN
IF(LTRIM(RTRIM(TO_CHAR(SYSDATE,’DAY’)))=’SATURDAY’)
THEN
RAISE_APPLICATION_ERROR(-20998,’Action Denied’);
END IF;
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

on saturday. You will get the message ‘Action Denied’. This is very useful feature used to avoid any alteration in sensitive data on weekends when you are not around and anybody else tries to alter it.

3. Program to illustrate the use of AFTER TRIGGER. This trigger will put the altered enteries in a new table named track.

Suppose student table is very critical and crucial. Only you are allowed to alter the enteries. So you can make trigger to track other persons who login with their username and try to alter the table. For this you have to create a table track as follows:

CREATE TABLE track (roll number, name varchar2(40), oldmarks number, newmarks number, uname varchar2(40));

Now create a Trigger as

CREATE OR REPLACE TRIGGER track_action AFTER UPDATE ON student FOR EACH ROW
BEGIN
INSERT INTO track VALUES(:OLD.roll, :OLD.name, :OLD.marks, :NEW.marks, USER);
END;

Now suppose a relative of TANK works in your department and he comes to increase his marks for 34 to 94. He will login from his username and will fire the following query.

UPDATE student SET marks = 94 WHERE roll = 20036;

He will now get delighted that he has altered the table. But he doesn’t know that a secret table named track has strored all the alterations done by him.

Now when you come in morning and fire the following query:

SELECT * FROM track;

Now, there will be one record in this table containing roll as 20036, name as TANK, old marks as 34 and new marks as 94 and most importantly the username of sneaker. So he has been trapped.

3 Very Simple PLSQL Programs to Explain Procedures, Functions and Packages

Here in this tutorial, Procdures, Functions and Packages are fully explored through simple programs. Procedure is a subprogram that performs a given task while Function is same as Procedure but it returns the value. Packages group up Procedures, Functions, Variable, Constants, Cursors and Exceptions.

1. Program to illustrate the use of Procedure. The program is to multiply two numbers. This program is stored in a file name myprocedure.sql

CREATE OR REPLACE PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

Now use SQL>@ myprocedure to create this procedure.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> EXEC product(3,4); It will give output 12.

You can also call the above procedure through a program below:

SQL> ED CALLPRO
DECLARE
a number;
b number;
BEGIN
a:= &a;
b:= &b;
product(a,b);
END

SQL> @ CALLPRO;

2. Program to illustrate the use of Functions. The program is to multiply two numbers. This program is stored in a file name myfunction.sql

CREATE OR REPLACE FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

Now use SQL>@ myfunction to create this function.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> SELECT product2(3,4) FROM DUAL; It will give output 12.

You can also call the above function through a program below:

SQL> ED CALLFUN
DECLARE
a number;
b number;
result number;
BEGIN
a:= &a;
b:= &b;
result:= product2(a,b);
DBMS_OUTPUT.PUT_LINE(result);
END

SQL> @ CALLFUN;

3. Program to illustrate the use of Packages. This package name is combine and package specification is stored in file pack and package body is stored in file pack_body.

SQL> ED pack
CREATE OR REPLACE PACKAGE combine AS
PROCEDURE product(a number, b number);
FUNCTION product2(a number, b number) RETURN number;
END combine;

Now use SQL>@ pack to create this package.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make package body in file pack_body.

SQL> ED pack_body
CREATE OR REPLACE PACKAGE BODY combine AS

PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

END combine;

Now use SQL>@ pack_body to create this package body.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make call to this package as

SQL> EXEC combine.product(3,4);
SQL> SELECT combine.product(3,4) FROM DUAL;

9 Very Simple PLSQL Programs to Explain Control Structures

Here you will find the list of 9 simplest plsql programs fully exploring the control structures. This tutorial covers IF, ELSE, ELSIF, LOOP, EXIT, EXIT WHEN, WHILE, FOR, GOTO and NULL statements. So have a look...

1. Program to insert values in a table student which has two fields student_id and name.

DECLARE
max_id number;
BEGIN
SELECT MAX(student_id) INTO max_id FROM student;
INSERT INTO student (student_id, name) VALUES (max_id + 1, ‘Harry’);
DBMS_OUTPUT.PUT_LINE(’Record Inserted’);
END;

2. Program illustrating the use of IF, ELSE and ELSIF

If Percentage >=80 –> Grade A
If Percentage >=60 –> Grade B
If Percentage >=45 –> Grade C
If Percentage < 45 --> Fail

 

DECLARE
n number;
BEGIN
–Enter percentage between 1 and 100
n:=&n;
IF(n>=1 AND n< =100) THEN
IF(n>=80) THEN
DBMS_OUTPUT.PUT_LINE(’Grade A’);
ELSIF(n>=60 AND n<80)
DBMS_OUTPUT.PUT_LINE(’Grade B’);
ELSIF(n>=45 AND n<60)
DBMS_OUTPUT.PUT_LINE(’Grade C’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Fail’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(’Percentage must be between 0 and 100′);
END IF;
END;

3. Program illustrating the use of LOOP with EXIT statement

DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
IF(n>3) THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

4. Program illustrating the use of LOOP with EXIT WHEN statement

DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
EXIT WHEN n>3;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

5. Program illustrating the use of WHILE LOOP statement

DECLARE
n number:=0;
BEGIN
WHILE n<3
LOOP
n:=n+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

6. Program illustrating the use of FOR LOOP statement

DECLARE

n number:=0;

BEGIN
FOR i IN 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 6

7. Program illustrating the use of FOR LOOP REVERSE statement

DECLARE
n number:=0;
BEGIN
FOR i IN REVERSE 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 6

8. Program illustrating the use of GOTO statement

BEGIN
DBMS_OUTPUT.PUT_LINE(’First Line’);
GOTO third;
DBMS_OUTPUT.PUT_LINE(’Second Line’);
< >
DBMS_OUTPUT.PUT_LINE(’Third Line’);
END;

output:
First Line
Third LIne

9. Program illustrating the use of NULL statement

DECLARE
n number:= &n;
BEGIN
IF n MOD 2 = 0 THEN
DBMS_OUTPUT.PUT_LINE(’Number is Even’);
ELSE
NULL;
END IF;
END;

output: If entered no. is even then it will display the message otherwise no message will be displayed.

6 Very Simple PLSQL Programs to Explain Cursors

Cursors in PL/SQL are used to retrieve more than one row at a time. The data that is stored in cursors is known as Active Data Set. These cursors are of two types:

1. Implicit Cursors : predefined cursors
2. Explicit Cursors : user defined cursors

Here are simple 6 programs illustrating the concept of cursors.

1. Program to illustrate the use of attribute SQL%FOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
END;

2. Program to illustrate the use of attribute SQL%NOTFOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
END;

3. Program to illustrate the use of attribute SQL%ROWCOUNT in Implicit Cursor. The Program is to update the salary of each employee by 1000.

BEGIN
UPDATE emp SET emp_sal = emp_sal +1000;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘Records Updated’);
END;

4. Program to illustrate the use of Explicit Cursors. The Program is to display the information of employess (Emp No, Name and Salary) of a given department.

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata;
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;

5. Program to illustrate the use of Explicit Cursors with FOR LOOP. The Program is to display the information of employess of a given department (same as program 4)

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
BEGIN
FOR rec IN empdata
LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_no || rec.emp_name || rec.emp_sal);
END LOOP;
END;

6. Program to illustrate the use of Explicit Cursors with Parameter Passing Concept. The Program is to display the information of employees of a given department (same as program 4)

DECLARE
CURSOR empdata(n number) IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = n;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata(n);
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;