Infolinks

Friday, 13 July 2012

PL/SQL BEGIN

The BEGIN section

Can contain variable assignments, Embedded SQL and calls to other functions and procedures.

A BEGIN-END block can contain nested DECLARE-BEGIN-END sub blocks.
The use of nested sub-blocks allows the use of local variables with limited scope.

Each plsql block should be terminated with / on a line by itself.
BEGIN
   code block
   /
EXCEPTION
   code block
   /
END;

Exceptions

Oracle includes about 20 predefined exceptions (errors) - we can allow Oracle to raise these implicitly.

For errors that don't fall into the predefined categories - declare in advance and allow oracle to raise an exception.

For problems that are not recognised as an error by Oracle - but still cause some difficulty within your application - declare a User Defined Error and raise it explicitly
i.e IF x >20 then RAISE ...

Syntax:
EXCEPTION
   WHEN exception1 [OR exception2...]] THEN
   ...
   [WHEN exception3 [OR exception4...] THEN
   ...]
   [WHEN OTHERS THEN
   ...]
Where exception is the exception_name e.g. WHEN NO_DATA_FOUND... Only one handler is processed before leaving the block.

Trap non-predefined errors by declaring them You can also associate the error no. with a name so that you can write a specific handler.
This is done with the PRAGMA EXCEPION_INIT pragma.

PRAGMA (pseudoinstructions) indicates that an item is a 'compiler directive' Running this has no immediate effect but causes all subsequent references to the exception name to be interpreted as the associated Oracle Error.
-

Trapping a non-predefined Oracle server exception

DECLARE
   -- name for exception
   e_emps_remaining EXCEPTION
   PRAGMA_EXCEPTION_INIT (
      e_emps_remaining, -2292);
   v_deptno dept.deptno%TYPE :=&p_deptno;

BEGIN
   DELETE FROM dept
   WHERE deptno = v_deptno
   COMMIT;
EXCEPTION
   WHEN e_emps_remaining THEN
   DBMS_OUTPUT.PUT_LINE ('Cannot remove dept '||
   TO_CHAR(v_deptno) || '. Employees exist. ');
END;
When an exception occurs you can identify the associated error code/message with two supplied functions SQLCODE and SQLERRM
SQLCODE - Number
SQLERRM - message

An example of using these:
DECLARE
   v_error_code NUMBER;
   v_error_message VARCHAR2(255);

BEGIN

   ...

EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
   v_error_code := SQLCODE
   v_error_message := SQLERRM
   INSERT INTO t_errors
   VALUES ( v_error_code, v_error_message);
END;
Trapping user-defined exceptions
DECLARE the exception
RAISE the exception
Handle the raised exception

e.g.
DECLARE
  e_invalid_product EXCEPTION
BEGIN
   update PRODUCT
   SET descrip = '&prod_descr'
   WHERE prodid = &prodnoumber';
   IF SQL%NOTFOUND THEN
     RAISE e_invalid_product;
   END IF;
   COMMIT;
EXCEPTION
   WHEN e_invalid_product THEN
   DBMS_OUTPUT.PUT_LINE ('INVALID PROD NO');
END;
Propagation of Exception handling in sub blocks

If a sub block does not have a handler for a particular error it will propagate to the enclosing block - where it can be caught by more general exception handlers.
RAISE_APPLICATION_ERROR (error_no, message[,{TRUE|FALSE}]);

This procedure  allows user defined error 
messages from stored sub programs - call only from stored sub prog.
error_no = a user defined no (between -20000 and -20999)

TRUE = stack errors
FALSE = keep just last

This can either be used in the executable section of code or 
the exception section

e.g.
EXCEPTION
   WHEN NO_DATA_FOUND THEN
   RAISE_APPLICATION_ERROR (-2021,
        'manager not a valid employee');
END;
Standard Exceptions, from the the STANDARD package

Oracle Exception NameOracle ErrorExplanation
DUP_VAL_ON_INDEXORA-00001You attempted to create a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCEORA-00051A resource timed out, took too long.
TRANSACTION_BACKED_OUTORA-00061The remote portion of a transaction has rolled back.
INVALID_CURSORORA-01001The cursor does not yet exist. The cursor must be OPENed before any FETCH cursor or CLOSE cursor operation.
NOT_LOGGED_ONORA-01012You are not logged on.
LOGIN_DENIEDORA-01017Invalid username/password.
NO_DATA_FOUNDORA-01403No data was returned
TOO_MANY_ROWSORA-01422You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDEORA-01476Divide by zero error.
INVALID_NUMBERORA-01722Converting a string to a number was unsuccessful.
STORAGE_ERRORORA-06500Out of memory.
PROGRAM_ERRORORA-06501Generic "Contact Oracle support" message.
VALUE_ERRORORA-06502You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
ROWTYPE_MISMATCHORA-06504 
CURSOR_ALREADY_OPENORA-06511The cursor is already open.
ACCESS_INTO_NULLORA-06530 
COLLECTION_IS_NULLORA-06531 

No comments:

Post a Comment