Oracle SQL and PL/SQL (3 of 3)

  1. Declaring Variables
    1. List the benefits of PL/SQL
    2. PL/SQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL. This results in a robust, powerful language suited for designing complex applications. It is able to use control structure(IF-THEN,FOR,etc) like any other 3GL in combination with SQL.

      PL/SQL uses early binding to better performance when the block is called, however early binding has the side effect that all objects must exist (in order to be bined) at compile time , this restriction prohibits that PL/SQL use DDL (Data Definition Language ) since this type of manipulation would create new objects, which in turn would not be availabale when the compilation started.

      DML and transaction control statements are the only SQL statements that dont have the potential to modify schema objects or permissions on schema objects, thus they are the only legal SQL statements in PL/SQL. (The package DBMS_SQL allows some flexibilty to this restriction).

    3. Describe the basic PL/SQL block and its sections
    4. There are several types of PL/SQL blocks:

      • Anonymous Blocks: Are generally constructed dynamically and executed only once.
      • Named Blocks : Are anonymous blocks with a label that gives the block a name. These are also constructed dynamically and executed once.
      • Subprograms : Are procedures,packages and functions that are stored in the database. These blocks generally dont change once they are constructed, and they are executed many times. Subprograms are exectued explicitly via a call to the procedure,package or function.
      • Triggers : Are named blocks that are also stored in the database. They also generally dont change once they are constructed and are executed many times.Triggers are executed implicitly whenever the tirggrering event occurs.
      The basic block structure contains three distinctive sections :
      • DECLARATIVE SECTION : This is where all variables, cursors and types used by this block are located.Local procedures and functions can also be declared within this section. These subprograms will only be available for this block only.
      • EXECUTABLE SECTION : This is where the work of the block is done. Both SQL statements and procedural statements can appear in this section.
      • EXCEPTION SECTION : Code in this section is not executed unless an error occurs.

      The following is a typical structure used to delimit the areas of the block :

        DECLARE
        /* Declaritive section is here */
        BEGIN
        /* Executables section is here */
        EXCEPTION
        /* Exception section is here */
        END;
      
    5. Describe the significance of variables in PL/SQL
    6. In general, there are two forms of variables: scalar and composite .Scalar variables permit only a single value for the variable.Composite variables (Records or Tables) can contain multiple values of either identical or single data types.

    7. Declare PL/SQL variables
    8.   DECLARE
        nombre_articulo varchar2(42);
        precio number(5,2);
      

      A variable can also be of a type referenced from antoher table or variable, this is done with the argument %TYPE.

        DECLARE
         precio number(8,2);
         costo PRECIO%TYPE; -- References the datatype for precio
         descripcion articulos.tipo%TYPE; -- References the datatype for column
        tipo in table articulos
         BEGIN
           -------
         END;
      
    9. Execute a PL/SQL block
    10. See the following examples

  2. Writing Executable Statements
    1. Describe the significance of the executable section
    2. It contains all the DML language of the PL/SQL block

    3. Write statements in the executable section
    4. See the following examples

    5. Describe the rules of nested blocks
    6. Each block declares its variables in the DECLARATIVE SECTION and they are only valid for that Block,the same applies for EXCEPTION HANDLERS, they are valid only in the block. The use of packages makes the block rules more flexible.

    7. Execute and test a PL/SQL block
    8. See the following examples

    9. Use coding conventions
    10. Normally reserved words are all capitalized. There are no strict rules to these conventions, however there is a big benefit in establishing a system wide coding convention, as this a basic step in tuning applications, this allows for statments to be parsed and be reused, in case a query is syntatically the same as the other but an upper case letter is used instead of a lower case, then the statement will have to be reparsed causing a slight delay in the application.

  3. Interacting with the Oracle Server
    1. Write a successful SELECT statement in PL/SQL
    2. SELECT ---select_list_item--- INTO ---PL/SQL_record,variable--- FROM ---table_reference--- WHERE --- where_clause---
      
    3. Declare a datatype and size of a PL/SQL variable dynamically
    4.   DECLARE
         v_id nombres.id%TYPE;
         estudios escuela.nivel%TYPE;
      
    5. Write DML statements in PL/SQL
    6. See following examples

    7. Control Transactions in PL/SQL
    8. The commands COMMIT,ROLLBACK and SAVEPOINT can also be used inside the PL/SQL statements.

    9. Determine the outcome of SQL and DML statements
    10. See following examples

  4. Writing Control Structures
    1. Identify the uses and types of control strucutures
    2. They are normally used to update,delete or update batches of data. The structures are common to any 3GL : IF=THEN-ELSE, FOR,WHILE.

    3. Construct an IF statement
    4. See the following examples

    5. Construct and Identify different Loop Statements
      • IF-THEN-ELSE :
            DECLARE
            v_number_seats rooms.number_seats%TYPE;
            v_comment VARCHAR2(35);
            BEGIN
            SELECT number_seats
            FROM rooms
            WHERE room_id = 9000;
            IF v_number_seats < 50 THEN
            v_comment := 'Small';
            ELSIF v_number_seats < 100 THEN
            v_comment := 'Big';
            ELSE
            v_comment := 'Very Big';
            END IF;
            END;
          
      • IF-THEN-ELSE (With ORs) :
            DECLARE
            v_number1 NUMBER;
            v_number2 NUMBER;
            v_Result VARCHAR2(25);
            BEGIN
            ............
            IF v_number1 IS NULL OR
                 v_number2 IS NULL THEN
            v_Result := 'Unknown';
            ELSIF v_number1 < v_number2 THEN
            v_Result := 'Yes';
            ELSE
            v_Result := 'No';
            END IF;
            END;
          
      • (Loops) Simple Loops : In this type of loop the sequence of statements will be executed until the exit condition is reached.
            DECLARE
            v_Counter BINARY_INTEGER := 1;
            BEGIN
             LOOP
               INSERT INTO temp_table
                 VALUES (v_Counter,'Loop Index');
               v_Counter := v_Counter + 1;
              EXIT WHEN v_Counter > 50;
             END LOOP;
            END;
          
      • (Loops) While Loops : In this loop the condition is evaluated before each iteration in the loop. If the condition evaluates to TRUE the loop continues otherwise the loop ends.
            DECLARE
            v_Counter BINARY_INTEGER := 1;
            BEGIN
            WHILE v_Counter <= 50 LOOP
               INSERT INTO temp_table
                 VALUES (v_Counter,'Loop Index');
                 v_Counter := v_Counter + 1;
              END LOOP;
            END;
          
      • (Loops) Numeric For Loops : These type of Loops define beforhand the number of iterations the Loop will perform, this is contrary to the while and for loops, the numeric for Loop can also be executed in Reverse,note: The variable used by the numeric loop is implicitly declared as an BINARY_INTEGER :
            BEGIN
            FOR v_Counter IN 1..50 LOOP
              INSERT INTO temp_table
               values (v_Counter, 'Loop Index');
              END LOOP;
            END;
            BEGIN
            FOR v_Counter IN REVERSE 10..50 LOOP
                INSERT INTO temp_table
                 values (v_Counter,'Loop Index');
              END LOOP;
            END;
          
      • (Loops) Cursor For Loops : (See section on cursors)
    6. Use logic tables
    7. Control Back Flow using nested loops and labels
    8. Labels are used primarly in conjunction with GOTOs to send the execution of a program to a specific line, the labels are identified by <<name of label>>, however labels can also be used with other control structures.

        DECLARE
        v_Counter BINARY_INTEGER := 1;
        BEGIN
        LOOP
           INSERT INTO temp_table
             VALUES (v_Counter,'Loop Index');
             v_Counter := v_Counter + 1;
          IF v_Counter > 50 THEN
           GOTO 1_endofloop;
           END IF;
         END LOOP;
        <<1_endofloop>>
        INSERT INTO temp_table (char_col)
         VALUES ('Done!')
        END;
      

      A GOTO is illegal when the loop tries to branch into an inner loop ( that is,go into the loop, before checking the constraints on that loop).

      It is also illegal to branch from an IF clause to another (that is,being inside an IF a label cannot move the execution inside the (IFs) ELSE's clause.

      And finally it is illegal to branch from an exception handler back into the current block.

      A label inside other Control statements can also be used :

        BEGIN
           <<L_Outer>>
            FOR v_outerindex IN 1..50 LOOP
              .....
                <<L_Inner>>
                 FOR v_innerindex IN 1..50 LOOP
                   .....
                   IF v_outerindex > 40 THEN
                     EXIT L_outer; -- Exits both loops
      	     END IF;
                 END LOOP L_inner;
              END LOOP L_outer;
      
      SIDENOTE: NULL statement.In some cases within a control structure there may be a need to explicitly state that no action be taken, this is what the statement NULL does:

        DECLARE
           v_TempVar NUMBER:= 7;
         BEGIN
           IF v_number < 5 THEN
            INSERT INTO temp_table (char_col)
            VALUES ( 'Very Small');
           ELSIF v_Tempvar < 10 THEN
            INSERT INTO temp_table (char_col)
            VALUES ( ' OK ');
           ELSE
            NULL;
           END IF;
         END;
      

  5. Working with Composite Datatypes
    1. Create user-defined PL/SQL records
    2. PL/SQL records are similar to C structures. A record provides a way to deal with separate but related variables. The benefit of records is that all the variables can be manipulated as a unit.

        DECLARE
        TYPE t_StudentRecord IS RECORD (
         StudentID NUMBER(5),
         First_name VARCHAR2(40),
         Last_name VARCHAR2(40));
         /* Now you can declare a variable of this type */
         v_Studentinfo t_StudentRecord;
         ............
      

      A record can also have default values assigned to it.

        DECLARE
        TYPE t_automoviles IS RECORD (
        Transmision varchar2(15) :='Automatica',
        Descripcion varchar2(100),
        Modelo date );
        v_propietario t_automoviles;
      

      In order to assign values to a record (based on the declaration above):

        BEGIN
         v_propietario.Modelo := sysdate;
         v_propietario.Descrpcion := 'Un volkswagen';
      
    3. Create a Record with the %ROWTYPE attribute
    4. Similar to the %TYPE attribute a Record can be created with a reference to another table, thus the variables of the record will be composed of the columns of the refered table. This is done directly in the final declaration.

      v_relatives family_members%ROWTYPE
      

      The variable v_relatives will be a RECORD with the variables of the columns in family_members.

    5. Create a PL/SQL table
    6. PL/SQL Tables are similar to arrays in C. A PL/SQL is similar to a database table that contains a KEY and VALUE column. The type of key in a PL/SQL table is a BINARY INTEGER, and the type of value is whatever it was defined to be at the initial declaration.

        DECLARE
        TYPE t_charactername IS TABLE OF VARCHAR2(10)
        INDEX BY BINARY_INTEGER;
        v_character t_charactername;
      

      The %TYPE attribute can also be used:

        DECLARE
        TYPE t_tablename IS TABLE OF students.last_name%TYPE
        INDEX BY BINARY_INTEGER;
        v_table t_tablename
      

      In order to assign values to Table it can be done as (based on the previous example):

        BEGIN
        v_table(12) := 'Rubio';
        v_table(-31) := 'Veytia';
        END;
      

      The things to notice on PL/SQL tables are:

      • Tables are unconstrained.The only limit on the number of rows is the values that can be represented by the BINARY_INTEGER type.
      • The elements in PL/SQL table are not necessarily in any particular order Since they are not stored contiguosly in memory like an array, elements can be inserted under arbitrary keys.
      • The keys used in PL/SQL table dont have to be sequential. Any BINARY_INTEGER value or expression can be used for a table index.
    7. Create a PL/SQL Table of Records.
    8.   BEGIN
        TYPE t_StudentTable IS TABLE OF students%ROWTYPE
          INDEX BY BINARY INTEGER;
           v_Students t_StudentTable;
         BEGIN
          SELECT *
            INTO v_Students(10001)
            FROM students
            WHERE id = 10001;
        END;
      

      In order to refer to the fields with the table of records use:

        table(index).field
         v_students(10001).first_name := 'Daniel'
      
    9. Describe the difference between records, tables, and tables of records
    10. See previous example

      A Table also has various attributes that can be used in the PL/SQL block:

      (The following examples are based on the existence of:

          TYPE t_NumberTable IS TABLE OF NUMBER
          INDEX BY BINARY INTEGER;
          v_Numbers t_NumberTable
        
      )

      • COUNT: Return the current number of rows in a PL/SQL table:
        v_total:v_Numbers.COUNT
      • DELETE: Removes rows from a PL/SQL table:
        v_Numbers.DELETE(-5); -- Deletes rows with index 5
        v_Number.DELETE(7-12); -- Deletes rows with index 7 through 12
        v_Number.DELETE; --Deletes all rows in the PL/SQL table.
      • EXISTS: Returns TRUE if a row with the index is in the PL/SQL table.
      • FIRST and LAST : Returns the INDEX of the first and last rows in the PL/SQL table respectively.
      • NEXT and PRIOR : Returns the INDEX of the next and last INDEX specified in the statement.

  6. Writing Explicit Cursors
    1. Distinguish between an implicit and an explicit cursor
    2. In order to process an SQL statement,Oracle allocates an area of memory known as the context area. This area contains information necessary to complete the processing, including the number of rows processed by a statement, a pointer to the parsed representation of the statement, and in the case of query an active set , which is the set of rows returned by the query. A cursor is a handle,or pointer to the context area.Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed.

      An explicit cursor is when a cursor name is explictitly assigned to a select statement via the CURSOR..IS. An implicit cursor is used for all other SQL statements.

      Declaration of an explcit cursor:

        DECLARE
        v_StudentID students.id%TYPE;
        v_FirtsName students.first_name%TYPE;
        v_LastName students.last_name%TYPE;
        v_Major students.major%TYPE := 'Computer Science';
        CURSOR c_Students IS
          SELECT id,first_name, last_name
            FROM students
            WHERE major = v_Major;
        BEGIN
           OPEN c_Students
          LOOP
           FETCH c_Students INTO v_StudentID, v_FirstName,v_LastName;
           EXIT WHEN c_Students%NOTFOUND;
          END LOOP;
        CLOSE c_Students;
        END;
      

      Just like PL/SQL table attributes , CURSORS also posses the following :

      • %FOUND: Returns TRUE if the previous fetch returned a row and FALSE if it didnt.If the attibute is checked while the CURSOR is closed then an error is returned.
      • %NOTFOUND: If the prior fetch return a row, the %NOTFOUND is FALSE. It is mostly used as an exit condition ( as in the previous example )
      • %ISOPEN: This is used to determine if the associated cursor is open
      • %ROWCOUNT: Returns the number of row returned by a cursor so far

      NOTE: IMPLICIT cursors are known as SQL cursor , thus if you wish to use the attributes from an implict cursor (which in other words is every SELECT statement that retruns more than one row) you use SQL%NOTFOUND, SQL%ROWCOUNT...etc.

    3. Use a PL/SQL record variable
    4.   DECLARE
        CURSOR c_AllStudents IS
           SELECT * from students;
        v_StudentInfo c_AllStudents%ROWTYPE;
        BEGIN
        ....
        END;
      
    5. Write a cursor FOR loop
    6.   DECLARE
        CURSOR c_HistoryStudents IS
        SELECT id, first_name, last_name
          FROM students
          WHERE major='History';
        BEGIN
        --Begin Loop the cursor is implicitly OPEN
        FOR v_StudentData in c_HistoryStudents LOOP
        --An implicit fecth
        INSERT INTO registered_students (student_id, department,course)
        VALUES ( v_StudentData.ID,'HIS',301);
        INSERT INTO temp_table (num_col,char_col)
          VALUES (v_StudentData.ID,
                  v_StudentData.first_name || ' '|| v_StudentData.last_name);
         END LOOP;
        COMMIT;
        END;
      

  7. Advanced Explicit Cursor Concepts
    1. Write a cursor that uses parameters
    2.   CURSOR c_students (p_Department classes.department%TYPE
                                   p_Course classes.department%TYPE ) IS
        SELECT * FROM classes
        WHERE department = p_Department
        AND course = p_Course;
      

      To call the cursor:

      OPEN c_students('CS',101);
    3. Determine when a FOR UPDATE clause in a cursor is required
    4. The syntax for this parameter in the SELECT statement is:

      SELECT ... FROM ... FOR UPDATE [OF column_reference] [NOWAIT]

      where column_reference is a column in the table against which the query is performed. A list of columns can also be used.

        DECLARE
        CURSOR c_AllStudents IS
        SELECT *
          FROM students
          FOR UPDATE OF first_name, last_name;
      

      Or the cursor can select every column by not specifing a range

        DECLARE
        CURSOR c_AllStudents IS
        SELECT *
          FROM students
          FOR UPDATE;
      

      Normally a SELECT statement will not take any locks on the rows being accessed.This allows other sessions connected to the database to change the data being selected. At OPEN time, when the active set is determined, Oracle takes a snapshot of the table. Any changes that have been commited prior to this point are reflected in the acitve set. Any changes made after this point, even if they are commited, are not reflected unless the cursor is reopened, which will evaluate the active set again. However,if the FOR UPDATE clause is present, exclusive row locks are taken on the rows in the active set before the OPEN returns ( the cursor is CLOSED ). These locks prevent other sessions from changing the rows in the active set until the transaction is commited.

      If another session already has locks on the rows in the active set, then the SELECT FOR UPDATE will hang until the other session releases the lock. To handle this situation the parameter NOWAIT is available, which in case the rows are locked,OPEN will return the error ORA-54 resource busy and acquire with NOWAIT specified.

    5. Determine when to use a WHERE CURRENT OF clause.
    6. When a cursor is declared with a FOR UPDATE, the WHERE CURRENT OF clause can be used in an UPDATE or DELETE statement. The syntax is :

        WHERE CURRENT OF cursor
      

      where cursor is the name of a cursor that has been declared with a FOR UPDATE clause. The WHERE CURRENT OF evaluates to the row that was just retrieved by the cursor.

        DECLARE
        v_NumCredits classes.num_credits%TYPE;
        CURSOR c_RegisteredStudents IS
         SELECT *
         FROM students
         WHERE ID in ( SELECT student_id
                       FROM registered_students
                       WHERE department = 'HIS'
                       AND course=101)
         FOR UPDATE OF current_credits;
        BEGIN
         FOR v_StudentInfo IN c_RegisteredStudents LOOP
          SELECT num_credits
           INTO v_NumCredits
           FROM classes
           WHERE department='HIS'
           AND COURSE= 101;
          UPDATE students
           SET current_credits = current_credits + v_NumCredits
           WHERE CURRENT OF c_RegisteredStudents;
         END LOOP;
         COMMIT;
        END;
      

      Fetching Across COMMITS: Note that the COMMIT statement is done after the fetch loop is complete. This is done because COMMIT will release any locks held by the session. Since the FOR UPDATE clause acquires locks, these will be released by the COMMIT. When this happens, the cursor is invalidated. Any subsequent fetches will return the Oracle error: ORA-1002 : fetch out of sequenece . Thus if there is a COMMIT indside a SELECT FOR UPDATE fetch loop, any fetches done after the COMMIT will fail. So it is not advisable to use a COMMIT inside the LOOP. If the cursor is not defined with a SELECT FOR UPDATE then there is no problem.

      The other option available for updating a table without the use of FOR UPDATE and the WHERE CURRENT OF, is to use the primary key of the table :

        DECLARE
        v_NumCredits classes.num_credits%TYPE;
        CURSOR c_RegisteredStudents IS
           SELECT *
            FROM students
            WHERE id IN ( SELECT student_id
                          FROM registered_students
                          WHERE department = 'HIS'
                          AND course= 101 );
         BEGIN
            FOR v_StudentInfo IN c_RegisteredStudents LOOP
            SELECT num_credits
            INTO v_NumCredits
            FROM classes
            WHERE department = 'HIS'
            AND course = 101 ;
          UPDATE students
           SET current_credits = current_credits + v_NumCredits
           WHERE id = v_StudentInfo.id;
         COMMIT;
         END LOOP;
        END;
      

      The drawback of using this method is that it DOES NOT acquire locks on the rows in the active set. As a result, it may not perform as expected if the other sessions are accessing the data concurrently.

    7. Write a cursor that uses a subquery.
    8. See previous example

  8. Handling Exceptions
    1. Define PL/SQL exceptions
    2. By using exceptions and exception handlers, you can make your PL/SQL programs robust and able to deal with both unexpected and expected errors during execution. The two type of errors that occur are : compile time which are reported by the PL/SQL compiler, and at run time which are handled by the PL/SQL run-time engine.

      Exceptions are designed for run-time error handling, rather than compile time error handling. When an error occurs an exception is raised. When this happens control is passed to the exception handler, which is a separate section of the program. This separates the error handling from the rest of the program, which makes the logic of the program easier to understand. PL/SQL offers this capability in contrast to other programming languages which need the exception declaration EXACTLY after the statement that is being revised. The advantage to the exception handling being done in a separate block are:

      • Program logic is easier to understand , since it is clearly visible.
      • No matter which statement fails, the program will detect and handle the error.
      • Types of Exceptions
        • User Defined Exceptions: This is an error that is defined by the program. The error that it signifies is not necessarily an Oracle error- it could be an error with the data. User-defined exceptions are declared in the declaritive section of a PL/SQL block. Just like variables, excpetions have a type (EXCEPTION) and a scope.
          e_TooManyStudents EXCPETION;

          The previous declaration is an identifier that will be visible until the end of the block. Note that the scope of an exception is the same as the scope of any other variable or cursor in the same decalrative section.

        • Predefined Exceptions: These typically correpond to commonly SQL errors. Like the predefined types (VARCHAR2, NUMBER,etc) these exceptions are defined in the STANDARD package. Because of this it is not necessary to declare them in the declarative section, they are already available.

          User defined exceptions are raised explicitly via the RAISE statement, while predefined excpetions are raised implicitly when their associated Oracle error occurs. Predefined excpetions have the option of also being raised with the RAISE statement if necessary.

            DECLARE
             e_TooManyStudents EXCEPTION;
             v_CurrentStudents NUMBER(3);
             v_MaxStudents NUMBER(3);
             BEGIN
             SELECT current_students, max_students
               INTO v_CurrentStudents, v_MaxStudents
               FROM CLASSES
               WHERE department ='HIS' AND course=101;
             IF v_CurrentStudents > v_MaxStudents THEN
               RAISE e_TooManyStudents;
             END IF;
             END;
          
        • When an exception is raised, control immediatly passes to the exception section of the block. If there is no exception section, the exception is propagated to the enclosing block. Once control passes to the exception handler , there is NO WAY to return to the executable section of the block.

    3. Recognize Unhandled exceptions
    4. It is good programming practice to avoid unhandled exceptions. This can be done via an OTHERS handler at the TOPMOST level of your program. This handler may simply log the error and where it ocurred. This way, you ensure that no error will go undetected. Example:

        DECLARE
           v_ErrorNumber NUMBER -- Variable to hold the error number
           v_ErrorText VARCHAR2(200) -- Variable to hold the error message text
        BEGIN
          /* Normal PL/SQL Processing */
         ---
        EXCEPTION
          WHEN OTHERS THEN
            v_ErrorNumber := SQLCODE;
            v_ErrorText := SUBSTR(SQLERRM, 1, 200);
             INSERT INTO log_table (code, message, info) VALUES
            (v_ErrorNumber,v_ErrorText, 'Oracle error occurred at ' ||
        TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
        END;
      
    5. List and use different types of PL/SQL exeception handlers
    6. The exception section consists of handlers for all the exceptions. An exception handler contains the code that is executed when the error is associated with the excpetion occurs, and the exception is raised.

      The syntax for the exception section is as follows:

        EXCEPTION
        WHEN exception_name THEN
         sequence_of_statements;
        WHEN exception_name THEN
         sequence_of_statements_2;
        WHEN OTHERS THEN
         sequence_of_statements_3;
        END;
      

      An example would be:

        DECLARE
         e_TooManyStudents EXCEPTION;
         v_CurrentStudents NUMBER(3);
         v_MaxStudents NUMBER(3);
         BEGIN
          SELECT current_students, max_students
           INTO v_CurrentStudents, v_MaxStudents
           FROM classes
           WHERE department = 'HIS' AND course = 101;
          IF v_CurrentStudents = v_MaxStudents THEN
            RAISE e_TooManyStudents;
          END IF;
         EXCEPTION
           WHEN e_TooManyStudents THEN
            INSERT INTO log_table (info) values
            ('History 101 has' || v_CurrentStudents || 'students: max allowed is'
            || v_MaxStudents);
           WHEN OTHERS THEN
            INSERT INTO log_table (info) values ('Another error occurred');
         END;
      

      PL/SQL provides two built-in functions to identify which type of error is the exception that occrued , this is specially useful in the WHEN OTHERS clause in the exception block. The funcitions are SQLCODE and SQLERRM.

      It is necessary to assign the SQLCODE / SQLERRM to local variables prior to any other use of them because these functions are procedural and they cannot be used directly inside the SQL statement.

      SQLERRM can also be called directly, the argument provided to SQLERRM must be a negative number. If SQLERRM is called with any positive number other than +100, the message User-defined Exceptionis returned SQLERRM(100) returns ORA-1403: no data found.

    7. Trap unanticapted errors
    8. This is done via the WHEN OTHERS or WHEN NO_DATA_FOUND clause in the exception declarations of a PL/SQL block. In order to recognize in which part of the blocks or block the unanticipated error ocurred in , 2 methods are recommended for this problem.

      Increment a Counter identifying the SQL statement

        DECLARE
         v_SelectCounter := 1; -- Variable to hold the select statement number
        BEGIN
         SELECT ...
         v_SelectCounter :=2;
         SELECT ...
         v_SelectCounter :=3;
         SELECT ...
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             INSERT INTO log_table (info) VALUES ('No data found in select' || v_SelectCounter);
        END;
      

      The second method is to put each statement into its own sub-block:

      BEGIN BEGIN SELECT ... EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table(info) VALUE ('No data found in select 1'); END; BEGIN SELECT... EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table(info) VALUE ('No data found in select 2'); END; BEGIN SELECT... EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table(info) VALUE ('No data found in select 3'); END; END;
    9. Describe the effect of exception propagation in the nested block
    10. Exceptions are scoped just like variables. If a user-defined is propagated out of scope, it can no longer be referenced by name.

      In general, if a user-defined error is to be propageted out of a block, it is best to define the exception in a package so that it will still be available outside the block, or to use RAISE_APPLICATION_ERROR instead. An example without using a package:

        DECLARE
        ...
        ...
        BEGIN
          ...
          DECLARE
             e_UserDefinedException EXCEPTION;
          BEGIN
             RAISE e_UserDefinedException;
          END;
          EXCEPTION
            /* e_UserDefined_Exception is out of scope here -can only be handled
              by
              an OTHERS handler */
            WHEN OTHERS THEN
             /* Handle Error */
      END;
      

      Using a package :

        CREATE OR REPLACE PACKAGE GLOBALS
        /* This package contains global declarations. Objects declared here
           will be visible via qualafied references for any other blocks or
           procedures. Note that this package does not have a package body */
        /* A user defined exception */
        e_UserDefinedException EXCEPTION;
        END Globals;
      

      Given the package Globals the preceding example can be written as:

        DECLARE
        ...
        BEGIN
        ...
           BEGIN
           /* Note that we must qualify e_UserDefinedPackages with the package
           name */
            RAISE Globals.e_UserDefinedException;
           END;
        EXCEPTION
         /* Since e_UserDefinedException is still visible, we can handle it 
            explicitly */
         WHEN Globals.e_UserDefinedException THEN
          /* Handle Error */
        END;
      

      EXCEPTION PROPAGATION

      Exceptions Raised in the Executable Section

      • Propagation Type 1: Exception A is raised and handled in the sub-block
              DECLARE
                A EXCEPTION;
              BEGIN
                  BEGIN
                    RAISE A ; --Exception A is raised in the sub-block
                  EXCEPTION
                    WHEN A THEN -- A is also handled in the sub-block
                    ----
                  END;
                  -- Control Resumes HERE
              END;
        
      • Propagation Type 2: The exception is propagated to the enclosing block
            DECLARE
              A EXCEPTION;
              B EXCEPTION;
            BEGIN
                BEGIN
                  RAISE B ; --Exception B is raised in the sub-block
                EXCEPTION
                  WHEN A THEN -- No handler for B in the sub-block
                  ----
                END;
            EXECPTION
              WHEN B THEN -- Exception B is propagated to enclosing block and handled there
            ----
            END;
            --Control then passes out the enclosing block, which completes succesfully
        
      • Propagation Type 3: The exception is propagated to the enclosing block, where there is still no handler for it. The enclosing block completes with an unhadled exception
            DECLARE
              A EXCEPTION;
              B EXCEPTION;
              C EXCEPTION;
            BEGIN
                BEGIN
                  RAISE C ; --Exception C is raised in the sub-block
                EXCEPTION
                  WHEN A THEN -- No handler for C in the sub-block
                  ----
                END;
            EXECPTION
              WHEN B THEN -- Exception C is propagated to enclosing block, but there is no handler for it there either
              ----
            END;
            --The exception is then propagted out the calling environment. The enclosing block completes with an unhandled exception
        
      Exceptions Raised in the Declarative Section
      • Propagation Type 4: Declarative Error with only an inner block
            DECLARE
               v_Number NUMBER(3) := 'ABC'; -- Illegal assignment raises VALUE_ERROR
            BEGIN
              ----
            EXCEPTION
              WHEN OTHERS THEN -- Even though there is a WHEN OTHERS handler, it is not executed
             ----
            END;
             --The Block completes unsuccesfully with the VALUE_ERROR exception
          
      • Propagation Type 5: Declarative Error with only an outer block
            BEGIN
              DECLARE
                 v_Number NUMBER(3) := 'ABC'; -- Illegal assignment raises
            VALUE_ERROR
               BEGIN
                 ----
               EXCEPTION
                 WHEN OTHERS THEN -- Even though there is a WHEN OTHERS handler, it is not executed
               ----
               END;
            EXCEPTION
             WHEN OTHERS THEN --The exception handler in the outer block handles the exceptio
            END;
            -- Control passes out the enclosing block, which completes successfully.
           
      Exceptions Raised in the Exception Section
      • Propagation Type 6: Exception called within the same exception block.
          DECLARE
            A EXCEPTION;
            B EXCEPTION;
          BEGIN
            RAISE A; -- Exception A is raised
          EXCEPTION
            WHEN A THEN -- Exception is handled, and B is raised in A's handler
              RAISE B;
            WHEN B THEN -- Even though there is a handler for B here, it is not executed.
            ---- The exception is propagated out of the block
          END;
          -- The block completes unsuccessfully with unhandled exception B.
        
      • Propagation Type 7: Exception called within the same exception block, but with handler in the outer block.
            BEGIN
              DECLARE
                A EXCEPTION;
                B EXCEPTION;
            BEGIN
              RAISE A; -- Exception A is raised
            EXCPETION
              WHEN A THEN -- Exception is handled, and B is raised in A's handler
                RAISE B;
              WHEN B THEN -- Even though there is a handler for B here, it is not executed.
                ---- The exception is propagated out of the block
            EXCEPTION
              WHEN B THEN -- Exception B is handled in the outer block
            ----
            END;
            -- The block completes successfully and control passes to the calling environment.
          
      • Propagation Type 8: Exception re-called within the same exception
            DECLARE
              A EXCEPTION;
            BEGIN
              RAISE A; -- Exception A is raised
            EXCEPTION
              WHEN A THEN -- Exception is handled, and the error is logged
                INSERT INTO ------;
               RAISE;-- The same excpetion is raised again
            END;
            -- The exception is propagated out of the block, which completes unsuccesfully with unhandled exception A.
          
    11. Customize PL/SQL exception messages
    12. You can associate a named exception with a Particular Oracle error. This gives you the ability to trap this error specifically, rather than via an OTHER handler. This is done via the EXCEPTION_INIT pragma. They are declared as:

      PRAGMA EXCEPTION_INIT (exception_name, Oracle_error_number);
      

      where exception_name is the name of an exception declared prior to the pragma, and Oracle_error_number is the desired error code to be associated with this named exception. This pragma must be in the declaritive section.

      The following example will raise the e_MissingNull user-defined exception if the >"ORA-1400:mandatory NOT NULL column missing or NULL during insert" error is encountered at run time.

        DECLARE
        e_MissingNull EXCEPTION;
        PRAGMA EXCPETION_INIT(e_MisingNull, -1400);
        BEGIN
          INSERT INTO students(id) VALUES (NULL);
        EXCEPTION
         WHEN e_MissingNull THEN
           INSERT INTO log_table (info) VALUES ('ORA-1400 occurred');
        END;
      

      Only one user-defined exception can be associated with an Oracle error with each occurence of PRAGMA EXCEPTION_INIT. Inside the exception handler, SQLCODE and SQLERRM will return the code and message for the Oracle error which occurred, rather than the user define message.

      Using RAISE_APPLICATION_ERROR

      You can use the built-in function RAISE_APPLICATION_ERROR to create your own error messages, which can be more descriptive that named exceptions. User-defined errors are passed out of the block the same way as Oracle errors to the calling environment. The syntax is

      RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
      

      where error_number is a parameter between -20,000 and -20,999 , error_message is the text associated with this error, and keep_errors is a boolean value. The boolean parameter, keep_errors is optional. If keep_errors is TRUE, the new error is added to the list of errors already raised (if one exists). If keep_errors FALSE, which is the default, the new error will replace the current list of errors. This declaration is done directly within the block like :

        BEGIN
        ----
        IF V_estudiantes + 1 > V_maximo_estudiantes THEN
          RAISE_APPLICATION_ERROR (-20000, 'No es posible realizar la transaccion');
        END IF;
        ---
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
           RAISE_APPLICATION_ERROR (-20001, 'No existen los datos');
        END;