Home arrow Oracle arrow Page 3 - Developing Simple PL/SQL Stored Procedures for CRUD Operations

Validate information before inserting a row using a PL/SQL stored procedure: explanation - Oracle

In this article, I shall go through a set of PL/SQL stored procedures which are very frequently used for CRUD operations. These stored procedures are mainly helpful for the developers who develop client applications (involving business logic or user interface design and programming) and who need a data layer to be implemented using PL/SQL stored procedures. The article is not targeted at pure PL/SQL developers.

  1. Developing Simple PL/SQL Stored Procedures for CRUD Operations
  2. Validate information before inserting a row using a PL/SQL stored procedure: code
  3. Validate information before inserting a row using a PL/SQL stored procedure: explanation
  4. How to update a row in a table using a PL/SQL stored procedure
  5. Deleting and retrieving values using PL/SQL stored procedures
By: Jagadish Chatarji
Rating: starstarstarstarstar / 41
February 19, 2007

print this article



This section explains the code provided in the previous section.  Let me start with the following:

      Invalid_sal       exception;
     Invalid_deptno    exception;

“Invalid_Sal” and “Invalid_Deptno” are two user-defined exceptions which can be raised within our logic according to our requirements.  The following is the code fragment which checks for the validity of salary:

      if p_sal<100 or p_sal>10000 then
            raise invalid_sal;
      end if;

In the above code fragment, if the salary is not in between 100 and 10000, it raises the user-defined exception “Invalid_sal.”  Once it is raised, the control (or flow of execution) jumps to the exception section, and gets the following to be executed:

      when invalid_sal then
            raise_application_error(-20001, 'Salary must be in
between 100 and 10000');

The above statement returns a user-defined message to the application which called the stored procedure.

Coming to the department validation, I need to check whether the given department number exists in the “dept” table or not.  If it is not available, I would like to raise an exception and send a message back to the user.  The following is the PL/SQL block which is nested into the main block to handle the same:

            dummy_var   varchar(10);
            select 'exists' into dummy_var
            from dept
            where deptno = p_deptno;
            when no_data_found then
                  raise Invalid_deptno;
            when others then
                  raise_application_error(-20011, sqlerrm);

The above checks to see whether the department exists or not.  If it doesn’t exist, the SELECT statement fails.  If it fails, “no_data_found” gets raised, which in turn fires the “invalid_deptno” exception.  If any other error occurs, it simply raises a default error.

If the “Invalid_deptno” exception is raised, control (or flow of execution) jumps to the following:

      when invalid_deptno then
            raise_application_error(-20001, 'Department doesn''t exist');

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: