--%>
Assignment Help - homework help

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 : Explain Variable declaration Variable

    Variable declaration: It is the association of a variable with a specific type. It is significant to make a distinction among the declaration of variables of primitive types and such of class types. The variable of primitive type performs as a contain

    		•
  • Q : What is an Infinite recursion Infinite

    Infinite recursion: Recursion which does not finish. This can effect from any of direct recursion, indirect recursion or the mutual recursion. It is generally the outcome of a logical error, and can consequence in stack overflow.

    		•
  • Q : Computer science 1. Here is a short

    1. Here is a short program. It prints out the value of a variable "x". Ernie and Bert disagree about what will be printed: Ernie says, the value gets changed in "changeX" so it will print "7", and Bert says, no, when the function exits the changes get reversed and the value goes back to "5". Explain

    		•
  • Q : State Cout and Cin Cout: This is an

    Cout: This is an object of ostream_withassign class stated in iostream.h header file

    		•
  • Q : Define the term Interprocess

    Define the term Interprocess communication: It is the ability of two or more separate processes to communicate with each other.

    		•
  • Q : Define Constant Constant : It is the

    Constant: It is the memory block where value can be stored once although can’t modify later on throughout program execution. Example: const int pi =3.14;

    		•
  • Q : Write a program that prints out all

    Write a program that prints out all prime numbers between 1 and 1000. Print the values out ten per line, with digits lined in proper columns.

    		•
  • Q : Simple Sorting Program in C Shell

    Assignment: Sorting You will write a simple sorting program. This program should be invoked as follows: shell% ./fastsort -i inputfile -o outputfile

    		•
  • Q : Describe Unnamed package Unnamed

    Unnamed package: All classes defined in the files without a package declaration are placed in an unnamed package.

    		•
  • Q : Define the need of process relative

    Define the need of process relative handles.

    		•