iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 06

“Character is like a tree and reputation like its shadow. The shadow is what we think of it; the tree is the real thing.” Abraham Lincoln (1809 - 1865), Lincoln's Own Stories

 

Read first then play the video:

   PLS-VIDEO -EXECPTION

  

EXCEPTION

 

Hands-On introduction

In this Hands-On, you modify the “test_for_loop3” file from previous Hands-On. You need to add exception to the PL/SQL block.

 

The PL/SQL EXCEPTION section is a place that handles your errors that occurs in the execution time.

 

In the exception section, you will add the “others” pre-defined exception to inform a user if the department number variable contains an invalid number. Then you add a user defined exception, to check the total number of employees in a department. You check to see if the total number of employees was less than 10, then the procedure raises an exception and prints a message - “We need more good employees.” Then save the file in the iself directory as “test_exception.sql.”

 

Go to “MS-DOS.” Change directory to the iself directory. And login to “SQLPLUS” as “iself/schooling.”

 

Set the serveroutput option to on.

SQL> set serveroutput on

 

Error handling without EXCEPTION

Open the notepad editor. Open the “test_for_loop3” file from the iself directory.

Modify the PL/SQL block to assign “a” to the department number variable.

 

(Notepad)

DECLARE

    v_deptno NUMBER(2);

BEGIN

    -- assign deptno 10

    v_deptno := ‘a’;

END;

.

Save the file in the iself directory as “c:_exception.sql”

Go to “SQLPLUS.” Run the file.

SQL> @test_exception

It will crash. Why? It crashes since the program didn’t know how to handle the errors.

 

Error handling with EXCEPTION

Oracle Defined EXCEPTION

Go back to notepad. Add exceptions to the PL/SQL block. In the exception section, check if any exceptions happened then print “Invalid Department number.”

 

(Notepad)

 

DECLARE

    v_deptno NUMBER(2);

 

BEGIN

    -- assign deptno 10

    v_deptno := ‘a’;

 

EXCEPTION

 

    -- example of PL/SQL define exception

    WHEN invalid_number THEN

            DBMS_OUTPUT.PUT_LINE

                    (‘Invalid deptno: ‘ || v_deptno);

 

    -- for others

    WHEN others THEN

            DBMS_OUTPUT.PUT_LINE (‘Other problem.’);

 

END;

/

Save the file.

Go to “SQLPLUS.” Run the file again.

SQL> @test_exception

 

Notice that the PL/SQL execution will not crash this time. Why?

 

User Defined EXCEPTION

Go back to notepad. Now, add the user defined exception to raise an exception if the number of employees is less than 10. In the declaration section, declare a user defined exception. In the execution section, check if the total number of employees is less than 10 then raise the exception. In the exception section, check if the exception was raised then print “we need more good employees.”

 

(Notepad)

DECLARE

 

    -- define department statistics

    CURSOR c_ds (p_deptno dept.deptno%type) IS

        SELECT dname, count (*) ttemp,

                sum(sal) ttsal, avg(sal) avsal

            FROM dept d, emp e

            WHERE d.deptno = e.deptno

            GROUP BY dname;

 

    -- define deptno variable

    v_deptno NUMBER(2);

    not_enough_emp EXCEPTION;

 

BEGIN

 

    -- assign deptno 10

    v_deptno := ‘a’;

 

    -- loop to read cursor record.

    FOR v_ds in c_ds (v_deptno) LOOP

 

        IF v_ds.ttemp < 10 THEN

            raise not_enough_emp;

        END IF;

 

        -- insert into dept_stat

        INSERT INTO dept_stat

            VALUES (v_ds.dname, v_ds.ttemp,

                                v_ds.ttsal, v_ds.avsal);

 

    END LOOP;

 

    -- save the insert transaction.

    COMMIT;

 

EXCEPTION

 

    -- example of user define exception

    WHEN not_enough_emp THEN

        dbms_output.put_line(‘We need more employees’);

 

    -- check deptno

    WHEN invalid_number THEN

        dbms_output.put_line(‘Invalid deptno: ‘ || v_deptno);

 

    WHEN others THEN

        dbsm_output.put_line(‘Other problem.’);

 

END;

/

 

Save the file.

Go to “sqlplus.”

Run the file again.

SQL> @test_exception

Notice that the first exception terminates the PL/SQL block.

 

Correct the error

Now, go back to the notepad editor and correct the error to eliminate the first exception.

 

(Notepad)

DECLARE

 

    -- define department statistics

    CURSOR c_ds (p_deptno dept.deptno%type) IS

        SELECT dname, count (*) ttemp,

                sum(sal) ttsal, avg(sal) avsal

            FROM dept d, emp e

            WHERE d.deptno = e.deptno

            GROUP BY dname;

 

    -- define deptno variable

    v_deptno NUMBER(2);

    not_enough_emp EXCEPTION;

 

BEGIN

 

    -- assign deptno 10

    v_deptno := 10;

 

    -- loop to read cursor record.

    FOR v_ds in c_ds (v_deptno) LOOP

 

        IF v_ds.ttemp < 10 THEN

            raise not_enough_emp;

        END IF;

 

        -- insert into dept_stat

        INSERT INTO dept_stat

                VALUES (v_ds.dname, v_ds.ttemp,

                                v_ds.ttsal, v_ds.avsal);

 

    END LOOP;

 

    -- save the insert transaction.

    COMMIT;

 

EXCEPTION

    -- example of user define exception

    WHEN not_enough_emp THEN

        dbms_output.put_line(‘We need more employees’);

 

    -- check deptno

    WHEN invalid_number THEN

        dbms_output.put_line(‘Invalid deptno: ‘ || v_deptno);

 

    WHEN others THEN

        dbsm_output.put_line(‘Other problem.’);

 

END;

/

Save the file.Run the file.

 

Now, you should not have any error.

 

“He who has a thousand friends has not a friend to spare, and he who has one enemy will meet him everywhere.” Ali ibn-Abi-Talib (602 AD - 661 AD), A Hundred Sayings

 

Questions:

Q: What is the EXCEPTION section in the PL/SQL language?

Q: What do you use the EXCEPTION section for?

Q: What would be happen if you don’t define your exception in the PL/SQL procedure?

Q: What is an Oracle Defined EXCEPTION?

Q: What is a User Defined EXCEPTION?

Q: What are the differences between a User Defined and an Oracle defined exceptions?

Q: Modify the previous PL/SQL block--last assignment in the previous hands-on practice--to add a user defined exception, to check the total number of employees in a department. Check if the total number of employees less than 10 then the procedure raises an exception and print a message - “We need more good employees.”