Oracle 11g PL/SQL Programming Brewbean’s wants to add a check constraint on the

Question

Oracle 11g PL/SQL Programming

Brewbean’s wants to add a check constraint on the QUANTITY column of the
BB_BASKETITEM table. If a shopper enters a quantity value greater than 20 for an item,
Brewbean’s wants to display the message “Check Quantity” onscreen. Using a text editor, open
the assignment04-06.txt file in the Chapter04 folder. The first statement, ALTER TABLE,
must be executed to add the check constraint. The next item is a PL/SQL block containing an
INSERT action that tests this check constraint. Add code to this block to trap the check
constraint violation and display the message.

Explanation / Answer

SET serveroutput ON;

DECLARE

ex_basket EXCEPTION;

pragma EXCEPTION_INIT(ex_basket, -02290);

BEGIN

EXECUTE IMMEDIATE 'ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity < 20);';

INSERT INTO testab VALUES

    (88

   );

EXCEPTION

WHEN ex_basket THEN

dbms_output.put_line('Check Quantity');

END;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.