Loops and Exceptions in Oracle

Loops and Exceptions in Oracle

1.What are the loops available oracle?

LOOPS: There are three types
1. Simple loop
2. While loop
3. for loop

1.Simple:

Ex:Declare
A number(2) := 1;
Begin
DBMS_OUTPUT.PUT_LINE( ‘welcome’ );
LOOP
DBMS_OUTPUT.PUT_LINE( ‘HELLO1’ );
DBMS_OUTPUT.PUT_LINE( ‘HELLO2’ );
Exit when A = 4;
A := A + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE( ‘THANK YOU’ );
END;
/

2.While:

Ex;Declare
A number(2) :=1;
Begin
DBMS_OUTPUT.PUT_LINE( ‘WELCOME’ );
While A <=4 loop
DBMS_OUTPUT.PUT_LINE( ‘HELLO1’ );
DBMS_OUTPUT.PUT_LINE( ‘HELLO2’ );
A := A + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE( ‘THANK YOU’ );
END;
/

3.FOR LOOP:

Numeric     FOR_LOOP ieterates over a specified range of integers.The range is part of an interation scheme,Which is enclosed by the keywords FOR and LOOP.
Ex:Declare
A number;
Begin
DBMS_OUTPUT.PUT_LINE( ‘WELCOME’ );
FOR A IN 1 .. 4 LOOP
DBMS_OUTPUT.PUT_LINE( ‘HELLO1’ );
DBMS_OUTPUT.PUT_LINE( ‘HELLO2’ );
END LOOP;
DBMS_OUTPUT.PUT_LINE( ‘THANK YOU’ );
END;
/

Note: in for loop the loop variable is implicitly declare.

4.What is the  Exceptions?

A:Runtime Errors are called as Exceptions. They are three types of Exceptions.
1.ORACLE Predefined Exception
2.ORACLE Non Predefined Exception
3.USER Defined Exception

5.Oracle Predefined Exception:

A:These Exceptions will have Exception name and Exception number. Examples of predefined Exceptions are:
       1.NO_DATA_FOUND
       2.TOO_MANY_ROWS
       3.ZERO_DIVIDE
       4.VALUE_ERROR
       5.DUP_VAL_ON_INDEX

6.USER DEFINED EXCEPTIONS:

A:These Exceptions are defined and controlled by the user. These Exceptions neither have predefined name nor have predefined number. Steps to handle user defined Exceptions.
Step1: Declare the Exception
Step2: Raised the Exception
Step3: Catch the Exception

7.ORACLE NON PREDEFINED EXCEPTIONS:

A:These Exceptions will have only Exception number. But does not have Exception name.
Steps to handle non predefined exceptions.
Syntax:
Step1: Declare the Exception
    <EXCEPTION_NAME> EXCEPTION;
Step2: Associate the Exception
    PRAGMA EXCEPTION_INIT(<EXCEPTION_NAME>,<EXCEPTION NO>);

Step3: Catch the Exception
    WHEN <EXCEPTION_NAME> THEN
    ------------------
    ------------------
    ------------------
    END;
       /
8.what is RAISE_APPLICATION_ERROR ?Why we are using this one in oracle?

A:RAISE_APPLICATION_ERROR is a procedure which is used to throw one error number and error message to the calling environment.
It internally performance rolls back.
ERROR number should be range of -20000 to -20999. ERROR message should be displayed less then or equal to 512 characters.

9.How many error reporting Functions are available in oracle?

A:They are two Error Reporting functions.
1. SQLCODE
2. SQLERRM

These error reporting functions are used in when others clause to identified the exception which is raised.
1.SQLCODE: It returns ERRORCODE
2.SQLERRM: It returns Exception number and Exception message.

Note: for NO_DATA_FOUND Exception SQLCODE will return 100.

10.VALUE_ERROR:

A:This Exception is raised when there is miss match with the value and data type of local variable or size of local variables.

11.NO_DATA_FOUND Exception:

A:This Exception is raised when select statement does not return any Row.

Ex:     Declare
    L_sal emp.sal%type;
    Begin
    DBMS_OUTPUT.PUT_LINE( ‘WELCOME’ );
    Select sal INTO L_sal from emp where empno = &empno;
    DBMS_OUTPUT.PUT_LINE(L_sal);
    DBMS_OUTPUT.PUT_LINE( ‘THANK YOU’ );
    EXCEPTION
    when NO_DATA_FOUND then
    DBMS_OUTPUT.PUT_LINE( ‘INVALID EMPNO’);
    END;
       /

0 comments:

Post a Comment