Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL: Nested Blocks in Depth

SQLCODE and SQLERRM - Oracle

This is part eight of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I gave an introduction to user defined exceptions and nested blocks in PL/SQL. In this article, we will look into handling more than one exception and different tips on using nested blocks.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Nested Blocks in Depth
  2. Labeling the PL/SQL blocks
  3. EXCEPTION handling in both parent and nested PL/SQL blocks
  4. SQLCODE and SQLERRM
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
July 12, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Those two are called as error reporting functions in PL/SQL. SQLCODE and SQLERRM give the code and error message respectively. Those values can be directly used within the PL/SQL program during the handling of exceptions. The following example demonstrates this.

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line('Name: '||v_ename1);

Exception
  When no_data_found then
    dbms_output.put_line('Employee not found with '||v_sal1);
  When others then
    dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);

End;

The above example does not handle the TOO_MANY_ROWS exception. Indeed, it gets automatically handled with the OTHERS exception. Within that exception, we are displaying both our own message along with the message given by the system for that error. SQLERRM and SQLCODE are generally used in the OTHERS exception, when you really don't know what type of exception gets raised during execution.

SQLERRM already includes SQLCODE within the message. So you need not use SQLCODE in the above scenario. But if you really want to use it, you can follow the example given below:

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line('Name: '||v_ename1);

Exception
  When no_data_found then
    dbms_output.put_line('Employee not found with '||v_sal1);
  When others then
    if SQLCODE = -1422 then
      dbms_output.put_line('More than one employee exists with the same salary');
    else
      dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);
    end if;

End;

In the above program -1422 is the SQL error code pre-assigned to the exception TOO_MANY_ROWS (which gets shown in the SQLERRM). And I hope the rest is same.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- 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: