--%>

Perform Exception Handling with User-Defined Errors

On occasion, some of Brewbean’s customers mistakenly leave an item out of a basket already checked out, therefore they create a new basket containing the missing items. Though they request that the baskets be combined and hence they are not charged extra shipping. The screen has been developed to permit an employee to modify the basket id of items in the BB_BASKETITEM table to another existing basket to merge the baskets. The block has been constructed to support this screen and can be found at the end of this question. Though an exception requires to be added to trap the condition in which an invalid basket id is entered for original basket. In this situation, the UPDATE affects no rows however does not raise an Oracle error. The handler must display a message stating “invalid original basket id”. Employ a host variable named G_OLD with a value of 30 and a host variable named G_NEW with a value of 4 to give the values to the block. First confirm that no item rows exist in the BB_BASKETITEM table with a basket id of 30.
 
BEGIN
  UPDATE bb_basketitem
   SET idBasket = :g_new
   WHERE idBasket = :g_old;
END;
/

E

Expert

Verified

create or replace function "BOB_UPDATE"
(g_old in NUMBER,
g_new in NUMBER)
return VARCHAR2
is
OID NUMBER;
state_missing EXCEPTION;
begin
SELECT count(IDBASKET) INTO OID FROM BB_BASKETITEM WHERE IDBASKET=G_OLD GROUP BY  IDBASKET ;
IF OID IS NULL THEN
  RAISE state_missing;
ELSE
   UPDATE bb_basketitem SET idBasket =g_new WHERE idBasket =g_old;
END IF;
RETURN 'UPDATED SUCCESSFULLY';
EXCEPTION
   WHEN state_missing THEN
      RETURN 'INVALID BASKET ID';
   WHEN OTHERS THEN
      RETURN 'INVALID BASKET ID';
end;


Testing Code:   
SELECT BOB_UPDATE(30,4) from dual;

   Related Questions in Programming Languages

  • Q : Questions on java class Array 1.

    1. Suppose we have a class A which has a constructor that takes a single integer. (a) After the following statements have been executed, how many A objects will exist

  • Q : Define Swapping Swapping : An

    Swapping: An {operating system} is frequently able to run programs which need more memory than is physically accessible on the host system. In order to do this, the complete memory needed is broken down into smaller pieces that are swapped in whenever

  • Q : Explain Structured programming

    Structured programming: It is a style of programming generally related with languages like FORTRAN, C, Pascal and so forth. Employing structured programming methods, a problem is frequently resolved employing a divide and conquer approach like stepwis

  • Q : Explain Array initializer Array

    Array initializer: This is an initializer for an array. The initializer takes the position of separate creation and initialization steps. For example, the initializer int[] pair = { 4, 2, };

    Q : Define Applet Applet : The Applets are

    Applet: The Applets are Java programs based on the Applet or JApplet classes. They are closely related with the capability to give active content within the Web pages. They have numerous features that differentiate them from ordinary Java graphical ap

  • Q : System Architecture for Windows

    Tell me about the System Architecture for Windows Programming?

  • Q : Advantages of Object oriented

    Q. What are the advantages of Object Oriented Programming languages? Explain them. Ans. : Object oriented programming language has following advantages-

    Q : Define the synchronization objects

    Define the synchronization objects. Ansewr: A synchronization object is use to co-ordinate the execution of many threads.

  • Q : Describe Uninitialized variable

    Uninitialized variable: It is a local variable which been declared, however has had no value allocated to it. The compiler will warn of variables that are employed before being initialized.

  • Q : What is Variable Variable : It is the

    Variable: It is the memory block of specific size where value can be stored and modified throughout program execution. Example: int x, float y, float amount, char c;