• Loading
    • PL/SQL Cursors And Exceptions

      Learning Objectives
      After completing this session, you will be able to:
      • Explain cursors
      • Explain the cursor attributes
      • Write cursors
      • Explain exceptions
      • Explain pre-defined exception
      • Explain user-defined exceptions
      • Describe PRAGMA EXCEPTION_INIT
      • Describe RAISE_APPLICATION_ERROR


      Understand Cursors


      A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement. In other words, a cursor is a SELECT statement that is defined within the declaration section of your PLSQL code. You will take a look at three different syntaxes for cursors. There are two types of cursors: Implicit and Explicit.
      • An Implicit cursor is used for all other SQL statements. Implicit Cursors gives less programmatic control.
      • In explicit cursor the cursor name is explicitly attached to a select statement


      The four PL/SQL steps necessary for explicit cursor processing are as follows:
      1. Declare the cursor
      2. Open the cursor
      3. Fetch the results into PL/SQL variables
      4. Close the cursor



      Declare the cursor
      To use a cursor, it must be declared first.
      Syntax
      Code:
      CURSOR cursor_name IS SELECT_statement;

      A cursor without parameters

      Code:
      CURSOR comp IS SELECT compid FROM company;

      A cursor with parameters

      Code:
      CURSOR comp (mcompid IN NUMBER) IS SELECT name FROM 
      company WHERE compid = mcomid;

      Open Cursors:

      Once you have declared your cursor, the next step is to open the cursor
      The basic syntax to OPEN the cursor is as follows:
      Code:
      OPEN cursor_name;
      For example, you could open a cursor called c1 with the following command:
      Code:
      OPEN c1;

      While opening a cursor:

      • The values of the bind variables are examined
      • Based on the bind variable the active set is determined
      • The active set pointer is set to the first row.


      Following is a function that demonstrates how to use the OPEN statement:
      Code:
      CREATE OR REPLACE Function FindCourse 
      ( name_in IN varchar2 )
       RETURN number 
      IS 
      cnumber number; 
      CURSOR c1 
      IS 
      SELECT course_number from courses_tbl where course_name = name_in;
       BEGIN 
      open c1;
       fetch c1 into cnumber; 
      if c1%notfound then
       cnumber := 9999;
       end if; 
      close c1; 
      RETURN cnumber;
       END;


      Fetch Cursor
      The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.
      Fetching a cursor has two forms:
      Code:
      FETCH cursor_name INTO list_of_variables;
      Or
      Code:
      FETCH cursor_name INTO PL/SQL_record;
      • After each FETCH, the active set pointer is increased to next row.
      • Thus, each FETCH will return successive rows in the active set, until the entire set is returned.
      • The %NOTFOUND attribute is used to determine when the active set has been retrieved.


      The basic syntax for a FETCH statement is:
      Code:
      FETCH cursor_name INTO <list of variables>;

      For example, you could have a cursor defined as:

      Code:
      CURSOR c1 IS SELECT course_number from courses_tbl where course_name = name_in;
      The command that would be used to fetch the data from this cursor is:
      Code:
      FETCH c1 into cnumber;
      This would fetch the first course_number into the variable called cnumber;

      Close Cursor

      The final step of working with cursors is to close the cursor once you have finished using it.
      The basic syntax to CLOSE the cursor is:
      Code:
      CLOSE cursor_name;
      For example, you could close a cursor called c1 with the following command:
      Code:
      CLOSE c1;

      Following is a function that demonstrates how to use the CLOSE statement:

      Code:
      CREATE OR REPLACE Function FindCourse
       (name_in IN varchar2)
       RETURN number
       IS cnumber number; 
      CURSOR c1 
      IS 
      SELECT course_number from courses_tbl where course_name = name_in; 
      BEGIN 
      open c1;
       fetch c1 into cnumber; 
      if c1%notfound then 
      cnumber := 9999; 
      end if; 
      close c1; 
      RETURN cnumber;
       END;

      Cursor Attributes:


      While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use: Attributes Explanation
      %ISOPEN Returns TRUE if the cursor is open, FALSE if the cursor is closed
      %FOUND Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
      Returns NULL if cursor is open, but fetch has not been executed.
      Returns TRUE if a successful fetch has been executed.
      Returns FALSE if no row was returned.
      %NOTFOUND Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
      Return NULL if cursor is open, but fetch has not been executed.
      Returns FALSE if a successful fetch has been executed.
      Returns TRUE if no row was returned.
      %ROWCOUNT Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
      Returns the number of rows fetched.



      Cursor Examples:
      The following example shows a procedure that outputs a dynamic PLSQL cursor. The example states a problem and shows how to solve it.

      Question:
      In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine" table.

      I've created an HTML Form where the user can enter any combination of three values to retrieve results from the "wine" table. My problem is that I need a general "select" statement that will work no matter what value(s), the user enters.

      Example:

      parameter_1= "Chianti"
      parameter_2= "10"
      parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?

      Code:
      SELECT * FROM wine
       WHERE column1 = parameter_1
       AND column2 = parameter_2 
      AND column3 = parameter_3;.

      The output of my stored procedure must be a cursor.
      Answer: To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle.

      Let's take a look at how we can do this. We've divided this process into 3 steps.
      Step 1 - Table Definition
      First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.
      Code:
      create table wine ( col1 varchar2(40), col2 varchar2(40), col3 varchar2(40) );
      We've made this table definition very simple, for demonstration purposes.

      Step 2 - Create package

      Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we can use a cursor as an output parameter in our stored procedure.
      Code:
      create or replace PACKAGE winepkg
       IS
       /* Define the REF CURSOR type. */ 
      TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE;
       END winepkg;
      This cursor will accept all fields from the "wine" table.

      Step 3 - Create stored procedure

      Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.

      The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).

      Code:
      create or replace procedure find_wine2 
      (col1_in in varchar2, col2_in in varchar2, col3_in in varchar2, c1 out winepkg.wine_type)
       as 
      BEGIN 
      /* all columns were entered */ 
      IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
       THEN 
      OPEN c1 FOR 
      select * from wine where wine.col1 = col1_in and wine.col2 = col2_in and wine.col3 = col3_in;
      
      /* col1 and col2 were entered */ 
      ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0) 
      THEN 
      OPEN c1 FOR 
      select * from wine where wine.col1 = col1_in and wine.col2 = col2_in; 
      
      /* col1 and col3 were entered */
       ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0) 
      THEN
       OPEN c1 FOR 
      select * from wine where wine.col1 = col1_in and wine.col3 = col3_in; 
      
      /* col2 and col3 where entered */
       ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0) 
      THEN 
      OPEN c1 FOR 
      select * from wine where wine.col2 = col2_in and wine.col3 = col3_in; 
      
      /* col1 was entered */ 
      ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0) 
      THEN 
      OPEN c1 FOR 
      select * from wine where wine.col1 = col1_in; 
      
      /* col2 was entered */ 
      ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0) 
      THEN 
      OPEN c1 FOR 
      select * from wine where wine.col2 = col2_in; 
      
      /* col3 was entered */ 
      ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0) 
      THEN 
      OPEN c1 FOR 
      select * from wine where wine.col3 = col3_in;  
      
      END IF;
       END find_wine2;

      Cursors FOR LOOP
      Cursors FOR LOOP
      • The cursor FOR loop is a shortcut to process explicit cursors.
      • Implicit open, fetch, exit, and close occur.
      • The record is implicitly declared.


      Syntax:

      Code:
      FOR record_name IN cursor_name LOOP 
      statement1; 
      statement2; 
      . . . 
      END LOOP;

      Example:

      Code:
      DECLARE 
      CURSOR c_product IS 
      SELECT product_key,product_name 
      FROM product_dim; 
      BEGIN 
      FOR i IN c_product LOOP 
      --implicit open and implicit fetch occur 
      IF i.product_key =1001 THEN 
      DBMS_OUTPUT.PUT_LINE ('Product ' || i.product_name); 
      END IF; 
      END LOOP; --implicit close and implicit loop exit 
      END;

      The For Update Clause


      The FOR UPDATE clause
      • Use explicit locking to deny access for the duration of a transaction.
      • Lock the rows before the update or delete.


      Example:

      Code:
      CURSOR c_product IS 
      SELECT loan_no,loan_amount 
      FROM daily_pipeline_loan_fact 
      WHERE product_key =1001 
      FOR UPDATE OF loan_amount NOWAIT;
      The FOR UPDATE clause identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update.

      The WHERE CURRENT OF Clause

      The WHERE CURRENT OF Clause
      • Use cursors to update or delete the current row.
      • Include the FOR UPDATE clause in the cursor query to lock the rows first.
      • Use the WHERE CURRENT OF clause to reference the current row from an explicit cursor.


      Example:
      Code:
      DECLARE 
      CURSOR c_loanamount IS 
      SELECT loan.loan_amount,loan.interest_rate,product.product_key 
      FROM daily_pipeline_loan_fact loan, product_dim product 
      WHERE loan.product_key= product.product_key 
      and product.product_key = 1001 
      FOR UPDATE OF loan_amount NOWAIT; 
      BEGIN 
      FOR i IN c_loanamount 
      LOOP 
      IF i.loan_amount < 1000 THEN 
      UPDATE daily_pipeline_loan_fact 
      SET interest_rate = i.interest_rate * 1.10 
      WHERE CURRENT OF c_loanamount; 
      END IF; 
      END LOOP; 
      END;


      Understanding Exceptions


      What is an Exception?
      • Exceptions are errors raised whenever there is any in a particular PL/SQL block. This causes a termination in the program by Oracle. Control then is transferred to the separate exception section of the program, if one exists, to handle the exception.
      • Oracle raises ERRORS whenever any abnormal situation arises in a PL/SQL block and performs an illegal termination of the execution of the program.
      • PL/SQL traps and responds to errors using architecture of exception handler.
      • Occurrence of any error in PL/SQL, whether a system error or an application error, an exception is rose.
      • This halts the processing in the current PL/SQL block's execution and control is transferred to the separate exception section of the program, if one exists, to handle the exception.
      • The control never returns to that block after you finish handling the exception. Instead, control is passed to the enclosing block, if any.
      • When an exception is raised, control passes to the exception section of the block. The exception section consists of handlers for all the exceptions.


      Code:
      EXCEPTION 
      WHEN exception_name THEN 
      sequence_of_statements1; 
      WHEN exception_name THEN 
      sequence_of_statements1; 
      END;

      Types of Exceptions:

      1. Predefined Exception
      2. User Defined Exception


      Predefined Exceptions


      Some exceptions are already defined in Oracle called the pre-defined exception. Mostly they are generated with the SELECT statement. They are raised implicitly at runtime. Every exception is associated with an error code. These exceptions are already defined in the STANDARD package (An Oracle supplied package). Oracle Exception Name Oracle Error Explanation
      DUP_VAL_ON_INDEX ORA-00001 You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
      TIMEOUT_ON_RESOURCE ORA-00051 You were waiting for a resource and you timed out.
      TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back.
      INVALID_CURSOR ORA-01001 You tried to reference a cursor that does not yet exist. This may have happened because you have executed a FETCH cursor or CLOSE cursor before opening the cursor.
      NOT_LOGGED_ON ORA-01012 You tried to execute a call to Oracle before logging in.
      LOGIN_DENIED ORA-01017 You tried to log into Oracle with an invalid username or password combination.
      NO_DATA_FOUND ORA-01403 You tried one of the following: 1. You executed a SELECT INTO statement and no rows were returned. 2. You referenced an uninitialized row in a table. 3. You read past the end of file with the UTL_FILE package.
      TOO_MANY_ROWS ORA-01422 You tried to execute a SELECT INTO statement and more than one row was returned.
      ZERO_DIVIDE ORA-01476 You tried to divide a number by zero.



      Exception functions:
      SQLCODE: Returns the numeric value for the error code.
      SQLERRM: Returns the message associated with the error number.

      Example:

      Code:
      DECLARE 
      v_err_code NUMBER; 
      v_err_text VARCHAR2(255); 
      v_product_name product_dim.product_name%type; 
      BEGIN 
      SELECT product_name into v_product_name 
      FROM product_dim 
      WHERE product_id=&input_product_id; 
      dbms_output.put_line(v_product_name); 
      EXCEPTION 
      WHEN NO DATA FOUND THEN 
      v_err_code:=SQLCODE; 
      v_err_text:=SQLERM; 
      insert into errors values (v_err_code,v_err_text) 
      commit; 
      END:

      User Defined Exception


      Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer or User-Defined Exceptions.

      The syntax for the named programmer-defined exception in a procedure is as follows:

      Code:
      CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] 
      exception_name EXCEPTION; 
      BEGIN executable_section 
      RAISE exception_name ; 
      EXCEPTION WHEN exception_name THEN [statements] 
      WHEN OTHERS THEN [statements] 
      END [procedure_name];

      The syntax for the named programmer-defined exception in a function is:
      Code:
      CREATE [OR REPLACE] FUNCTION function_name [(parameter [, parameter])] RETURN return_datatype IS | AS [declaration_section] 
      exception_name EXCEPTION; 
      BEGIN executable_section 
      RAISE exception_name ; 
      EXCEPTION WHEN exception_name THEN [statements] 
      WHEN OTHERS THEN [statements] 
      END [function_name];
      Here is an example of a procedure that uses a named programmer-defined exception:
      Code:
      CREATE OR REPLACE PROCEDURE add_new_order (order_id_in IN NUMBER, sales_in IN NUMBER) IS no_sales EXCEPTION; 
      BEGIN IF sales_in = 0 THEN RAISE no_sales; 
      ELSE INSERT INTO orders (order_id, total_sales ) VALUES ( order_id_in, sales_in ); END IF; 
      EXCEPTION WHEN no_sales THEN raise_application_error (-20001,'You must have sales in order to submit the order.'); 
      WHEN OTHERS THEN raise_application_error (-20002,'An error has occurred inserting an order.'); 
      END;

      PRAGMA EXCEPTION_INIT


      The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.
      • Some uncommon predefined errors numbers exists that does have names.
      • Oracle allows to associate a user-defined name with a predefined error number (Oracle Error).
      • A pragma called EXCEPTION_INIT instructs the compiler to associate or initialize a programmer-defined exception with a specific Oracle error number.
      • It is associated with a predefined number in the declarative part.


      Syntax:
      Code:
      PRAGMA EXCEPTION_INIT (EXCEPTION, ERROR_NUMBER);

      Example:

      Code:
      CREATE OR REPLACE PROCEDURE sp_addproduct( 
      IN V_prdouct_code Varchar2(100); 
      IN V_prdouct_name Varchar2(100);) AS 
      e_addproduct EXCEPTION; 
      PRAGMA EXCEPTION_INIT(e_addproduct,-22222); 
      BRGIN 
      UPDATE product_dim 
      SET product_name=V_prdouct_name 
      WHERE product_key=V_prdouct_code 
      IF SQL%NOTFOUND THEN 
      RAISE e_addproduct; 
      END IF; 
      COMMIT; 
      EXCEPTION WHEN e_addproduct THEN 
      DBMS_OUTPUT.PUT_LINE ('No such product id exist.') 
      END sp_addproduct;

      RAISE_APPLICATION_ERROR


      What is RAISE_APPLICATION_ERROR?
      The RAISE_APPLICATION_ERROR is a procedure that communicates application-specific errors from the server side (usually a database trigger) to the client-side application. This built-in procedure is the only mechanism available for communicating a server-side, programmer-defined exception to the client side in such a way that the client process can handle the exception. Error number should be of the range between –20000 and –20999. Error messages should be less than 512 characters.
      Syntax:
      Code:
      RAISE_APPLICATION_ERROR (error_number in NUMBER, error_msg in VARCHAR2);
      Example:
      Code:
      CREATE OR REPLACE PROCEDURE sp_addproduct( 
      IN V_prdouct_code Varchar2(100); 
      IN V_prdouct_name Varchar2(100);) AS 
      BEGIN 
      UPDATE product_dim 
      SET product_name=V_prdouct_name 
      WHERE product_key=V_prdouct_code 
      IF SQL%NOTFOUND THEN 
      RAISE_APPLICATION_ERROR(-20202,'This is not a valid product'); 
      END IF; 
      COMMIT; 
      END sp_addproduct;

      Summary
      • A SQL cursor is a private Oracle SQL working area.
      • The implicit cursor is used by Oracle server to test and parse the SQL statements.
      • Implicit cursors give less programmatic control.
      • An implicit cursor is used for all other SQL statements.
      • Explicit cursors are declared by the programmers. In explicit cursor the cursor name is explicitly attached to a select statement.
      • Explicit cursor handling requires declaring, opening, fetching, and closing cursor.
      • Oracle raises ERRORS whenever any abnormal situation arises in a PL/SQL block and performs an illegal termination of the execution of the program.
      • PL/SQL traps and responds to errors using architecture of EXCEPTION handler.
      • Pre-defined exceptions are already defined in the STANDARD package.
      • Exceptions can be pre-defined (built-in) and user-defined.


      Test Your Understanding

      • What is a cursor?
      • What is difference between implicit cursor and explicit cursor?
      • What are the different cursors attributes?
      • What is the use of FOR UPDATE clause?
      • What is the use of WHERE CURRENT OF?
      • What is the advantage of cursor with a FOR LOOP?
      • What are the different types of exception?
      • What is PRAGMA EXCEPTION_INT?
      • What is RAISE_APPLICATION_ERROR?
      • What is the use of SQLCODE and SQLERM?
      • Create a cursor to print empno, ename, job, sal, hiredate and the increment amount for all employees. Increment amount depends on the day of joining.
        Code:
        Day of Joining Increment in %
        Friday 20
        Wednesday 18
        Thursday 17
        For all others 15 Page
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.