Tuesday, 15 May 2018

PL/SQL - Exceptions

In this chapter, we will discuss Exceptions in PL/SQL. An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions −
  • System-defined exceptions
  • User-defined exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −
DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

Example

Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters −
DECLARE 
   c_id customers.id%type := 8; 
   c_name customerS.Name%type; 
   c_addr customers.address%type; 
BEGIN 
   SELECT  name, address INTO  c_name, c_addr 
   FROM customers 
   WHERE id = c_id;  
   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!'); 
END; 
/
When the above code is executed at the SQL prompt, it produces the following result −
No such customer!  

PL/SQL procedure successfully completed.
The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.

Raising Exceptions

Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception −
DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END;
You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way.

User-defined Exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is −
DECLARE 
   my-exception EXCEPTION;

Example

The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised.
DECLARE 
   c_id customers.id%type := &cc_id; 
   c_name customerS.Name%type; 
   c_addr customers.address%type;  
   -- user defined exception 
   ex_invalid_id  EXCEPTION; 
BEGIN 
   IF c_id <= 0 THEN 
      RAISE ex_invalid_id; 
   ELSE 
      SELECT  name, address INTO  c_name, c_addr 
      FROM customers 
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
   END IF; 

EXCEPTION 
   WHEN ex_invalid_id THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!');  
END; 
/
When the above code is executed at the SQL prompt, it produces the following result −
Enter value for cc_id: -6 (let's enter a value -6) 
old  2: c_id customers.id%type := &cc_id; 
new  2: c_id customers.id%type := -6; 
ID must be greater than zero! 
 
PL/SQL procedure successfully completed.

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions −
ExceptionOracle ErrorSQLCODEDescription
ACCESS_INTO_NULL06530-6530It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND06592-6592It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL06531-6531It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX00001-1It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR01001-1001It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER01722-1722It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED01017-1017It is raised when a program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND01403+100It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON01012-1012It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR06501-6501It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH06504-6504It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL30625-30625It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR06500-6500It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS01422-1422It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR06502-6502It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE014761476It is raised when an attempt is made to divide a number by zero.
at No comments:

Monday, 14 May 2018

Cursors And Bulk Collect

Cursors And Bulk Collect

CURSORS

Cursor is a pointer to memory location which is called as context area which contains the information necessary for processing, including the number of rows processed by the statement, a pointer to the parsed representation of the statement, and the active set which is the set of rows returned by the query.
Cursor contains two parts
  • Header
  • Body
Header includes cursor name, any parameters and the type of data being loaded.
Body includes the select statement.
Ex:
Cursor c(dno in number) return dept%rowtype is select *from dept;
In the above
Header – cursor c(dno in number) return dept%rowtype
Body – select *from dept
CURSOR TYPES
  • Implicit (SQL)
  • Explicit
  1. Parameterized cursors
  2. REF cursors
Explicit Cursor: The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly define a cursor to process the rows. You use three commands to control the cursor:
CURSOR STAGES
  1. Open
  2. Fetch
  3. Close
Implicit Cursors: ORACLE implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor.PL/SQL lets you refer to the most recent implicit cursor as the SQL” cursor. So, although you cannot use the OPEN,
FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information about the most recently executed SQL statement.
CURSOR ATTRIBUTES
  1. %found
  2. %notfound
  3. %rowcount
  4. %isopen
  5. %bulk_rowcount
  6. %bulk_exceptions
CURSOR DECLERATION
Syntax:
Cursor <cursor_name> is select statement;
Ex:
Cursor c is select *from dept;
CURSOR LOOPS
  1. Simple loop
  2. While loop
  3. For loop
SIMPLE LOOP
Syntax:
Loop
Fetch <cursor_name> into <record_variable>;
Exit when <cursor_name> % notfound;
<statements>;
End loop;
Ex:
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
WHILE LOOP
Syntax:
While <cursor_name> % found loop
Fetch <cursor_name> nto <record_variable>;
<statements>;
End loop;
Ex:
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
FOR LOOP
Syntax:
for <record_variable> in <cursor_name> loop
<statements>;
End loop;
Ex:
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
PARAMETARIZED CURSORS
  1. This was used when you are going to use the cursor in more than one place with different values for the same where clause.
  2. Cursor parameters must be in mode.
  3. Cursor parameters may have default values.
  4. The scope of cursor parameter is within the select statement.
Ex:

PACKAGED CURSORS WITH HEADER IN SPEC AND BODY IN PACKAGE BODY
  1. Cursors declared in packages will not close automatically.
  2. In packaged cursors you can modify the select statement without making any changes to the cursor header in the package specification.
  3. Packaged cursors with must be defined in the package body itself, and then use it as global for the package.
  4. You can not define the packaged cursor in any subprograms.
  5. Cursor declaration in package with out body needs the return clause.

REF CURSORS AND CURSOR VARIABLES
  1. This is unconstrained cursor which will return different types depends upon the user input.
  2. Ref cursors cannot be closed implicitly.
  3. Ref cursor with return type is called strong cursor.
  4. Ref cursor without return type is called weak cursor.
  5. You can declare ref cursor type in package spec as well as body.
  6. You can declare ref cursor types in local subprograms or anonymous blocks.
  7. Cursor variables can be assigned from one to another.
  8. You can declare a cursor variable in one scope and assign another cursor variable with different scope, then you can use the cursor variable even though the assigned cursor variable goes out of scope.
  9. Cursor variables can be passed as parameters to the subprograms.
  10. Cursor variables modes are in or out or in out.
  11. Cursor variables cannot be declared in package spec and package body (excluding subprograms).
  12. You cannot user remote procedure calls to pass cursor variables from one server to another.
  13. Cursor variables cannot use for update clause.
  14. You can not assign nulls to cursor variables.
  15. You cannot compare cursor variables for equality, inequality and nullity.

CURSOR EXPRESSIONS
  1. You can use cursor expressions in explicit cursors.
  2. You can use cursor expressions in dynamic SQL.
  3. You can use cursor expressions in REF cursor declarations and variables.
  4. You cannot use cursor expressions in implicit cursors.
  5. Oracle opens the nested cursor defined by a cursor expression implicitly as soon as it fetches the data containing the cursor expression from the parent or outer cursor.
  6. Nested cursor closes if you close explicitly.
  7. Nested cursor closes whenever the outer or parent cursor is executed again or closed or canceled.
  8. Nested cursor closes whenever an exception is raised while fetching data from a parent cursor.
  9. Cursor expressions cannot be used when declaring a view.
  10. Cursor expressions can be used as an argument to table function.
  11. You cannot perform bind and execute operations on cursor expressions when using the cursor expressions in dynamic SQL.
USING NESTED CURSORS OR CURSOR EXPRESSIONS
 CURSOR CLAUSES
  1. Return
  2. For update
  3. Where current of
  4. Bulk collect
RETURN
Cursor c return dept%rowtype is select *from dept;
Or
Cursor c1 is select *from dept;
Cursor c return c1%rowtype is select *from dept;
Or
Type t is record(deptno dept.deptno%type, dname dept.dname%type);
Cursor c return t is select deptno, dname from dept;
FOR UPDATE AND WHERE CURRENT OF
Normally, a select operation will not take any locks on the rows being accessed. This will allow other sessions connected to the database to change the data being selected. The result set is still consistent. At open time, when the active set is determined, oracle takes a snapshot of the table. Any changes that have been committed prior to this point are reflected in the active set. Any changes made after this point, even if they are committed, are not reflected unless the cursor is reopened, which will evaluate the active set again.
However, if the FOR UPDATE caluse is pesent, exclusive row locks are taken on the rows in the active set before the open returns. These locks prevent other sessions from changing the rows in the active set until the transaction is committed or rolled back. If another session already has locks on the rows in the active set, then SELECT … FOR UPDATE operation will wait for these locks to be released by the other session. There is no time-out for this waiting period. The SELECT…FOR UPDATE will hang until the other session releases the lock. To handle this situation, the NOWAIT clause is available.
Syntax:
Select …from … for update of column_name [wait n];
If the cursor is declared with the FOR UPDATE clause, the WHERE CURRENT OF clause can be used in an update or delete statement.
Syntax:
Where current of cursor;

BULK COLLECT
  1. This is used for array fetches
  2. With this you can retrieve multiple rows of data with a single roundtrip.
  3. This reduces the number of context switches between the pl/sql and sql engines.
  4. Reduces the overhead of retrieving data.
  5. You can use bulk collect in both dynamic and static sql.
  6. You can use bulk collect in select, fetch into and returning into clauses.
  7. SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.
  8. Bulk collect operation empties the collection referenced in the into clause before executing the query.
  9. You can use the limit clause of bulk collect to restrict the no of rows retrieved.
  10. You can fetch into multible collections with one column each.
  11. Using the returning clause we can return data to the another collection.
BULK COLLECT IN FETCH

BULK COLLECT IN SELECT

LIMIT IN BULK COLLECT
You can use this to limit the number of rows to be fetched.
 MULTIPLE FETCHES IN INTO CLAUSE
 RETURNING CLAUSE IN BULK COLLECT
You can use this to return the processed data to the ouput variables or typed variables.
POINTS TO REMEMBER
  1. Cursor name can be up to 30 characters in length.
  2. Cursors declared in anonymous blocks or subprograms closes automatically when that block terminates execution.
  3. %bulk_rowcount and %bulk_exceptions can be used only with forall construct.
  4. Cursor declarations may have expressions with column aliases.
  5. These expressions are called virtual columns or calculated columns.
at No comments:
Subscribe to: Posts (Atom)

Session Clear

 Select 'alter system kill session '''|| sid_serial#||''''||chr(010)||'/' from locked_objects_info_v...