Question: PL/SQL Auction Program 1. Create a user xyz, who is the owner of the auction. Create the schema, and package. 2. Create users x1 and

PL/SQL Auction Program

1. Create a user xyz, who is the owner of the auction. Create the schema, and package.

2. Create users x1 and x2 who are the participants in the auction. They will need acces to the package.

3. Bid on the same item and record your observations. Verify all scenarios. Upload the files with the missing code and a detailed sample run.

AUCTION OWNER.TXT

SQL> conn / as sysdba

Connected. SQL> drop user xyz cascade;

User dropped.

SQL> drop user x1 cascade;

User dropped.

SQL> drop user x2 cascade;

User dropped.

SQL> create user xyz identified by xyz account unlock quota 25M on users;

User created.

SQL> grant connect,resource to xyz;

Grant succeeded.

SQL> grant execute on dbms_alert to xyz;

Grant succeeded.

SQL> grant create view to xyz;

Grant succeeded.

SQL> create user x1 identified by x1 account unlock;

User created.

SQL> grant connect,resource to x1;

Grant succeeded.

SQL> create user x2 identified by x2 account unlock;

User created.

SQL> grant connect,resource to x2;

Grant succeeded.

SQL> conn xyz/xyz Connected. SQL> DROP VIEW high_bids; DROP VIEW high_bids * ERROR at line 1: ORA-00942: table or view does not exist

SQL> DROP TABLE bids; DROP TABLE bids * ERROR at line 1: ORA-00942: table or view does not exist

SQL> DROP TABLE auction_items; DROP TABLE auction_items * ERROR at line 1: ORA-00942: table or view does not exist

SQL> SQL> CREATE TABLE auction_items 2 (id VARCHAR2(20) NOT NULL PRIMARY KEY 3 ,description VARCHAR2(200) NOT NULL 4 ,min_bid NUMBER NOT NULL 5 ,curr_bid NUMBER 6 ,status VARCHAR2(10) 7 CONSTRAINT valid_status 8 CHECK (status IN ('OPEN','CLOSED') ) 9 );

Table created.

SQL> SQL> CREATE TABLE bids 2 (bidder VARCHAR2(30) 3 ,item_id VARCHAR2(20) 4 REFERENCES auction_items(id) 5 ON DELETE CASCADE 6 ,bid NUMBER NOT NULL 7 );

Table created.

SQL> SQL> CREATE OR REPLACE VIEW high_bids 2 (item_id 3 ,item_desc 4 ,bidder 5 ,high_bid) 6 AS 7 SELECT 8 BID.item_id 9 ,AI.description 10 ,BID.bidder 11 ,BID.bid 12 FROM 13 bids BID 14 ,auction_items AI 15 WHERE 16 BID.item_id = AI.id 17 AND BID.bid = (SELECT MAX(bid) 18 FROM bids B2 19 WHERE BID.item_id = B2.item_id) 20 /

View created.

SQL> SQL> -- End Auction.sql SQL> --Auction1 SQL> CREATE OR REPLACE PACKAGE auction 2 /* 3 || Implements a simple interactive bidding system 4 || using DBMS_ALERT to keep bidders informed 5 || of activity in items they are interested in. 6 || 7 || The item_id is used as the ALERT name for the 8 || item. 9 || 10 || 11 || Compilation Requirements: 12 || 13 || EXECUTE on DBMS_ALERT 14 || SELECT, UPDATE on AUCTION_ITEMS 15 || INSERT on BIDS 16 || SELECT on HIGH_BIDS 17 || 18 || Execution Requirements: 19 || 20 */ 21 AS 22 /* 23 || exceptions raised and handled in PLACE_BID 24 || procedure 25 */ 26 invalid_item EXCEPTION; 27 bid_too_low EXCEPTION; 28 item_is_closed EXCEPTION; 29 30 /* 31 || place a bid on an item, the bid must exceed any 32 || other bids on the item (and the minimum bid) 33 || 34 || bidding on an item registers interest in the 35 || item using DBMS_ALERT.REGISTER 36 || 37 || only this procedure should be used to add rows 38 || to the bids table, since it also updates 39 || auction_items.curr_bid column 40 */ 41 PROCEDURE place_bid 42 (item_id_IN IN VARCHAR2 43 ,bid_IN IN NUMBER); 44 45 /* 46 || close bidding on an item 47 */ 48 PROCEDURE close_item(item_id_IN IN VARCHAR2); 49 50 /* 51 || watch for any alerts on items bid by the user 52 || indicating other users have raised the bid 53 */ 54 PROCEDURE watch_bidding(timeout_secs_IN IN NUMBER:=300); 55 56 END auction; 57 /

Package created.

SQL> SQL> CREATE OR REPLACE PACKAGE BODY auction 2 AS 3 4 FUNCTION curr_bid(item_id_IN IN VARCHAR2) 5 RETURN NUMBER 6 IS 7 temp_bid NUMBER; 8 BEGIN 9 SELECT curr_bid 10 INTO temp_bid 11 FROM auction_items 12 WHERE id = item_id_IN; 13 14 RETURN temp_bid; 15 16 EXCEPTION 17 WHEN OTHERS 18 THEN RETURN NULL; 19 END curr_bid; 20 21 PROCEDURE place_bid 22 (item_id_IN IN VARCHAR2 23 ,bid_IN IN NUMBER) 24 IS 25 temp_curr_bid auction_items.curr_bid%TYPE; 26 temp_status auction_items.status%TYPE; 27 28 CURSOR auction_item_cur 29 IS 30 SELECT NVL(curr_bid,min_bid), status 31 FROM auction_items 32 WHERE id = item_id_IN 33 FOR UPDATE OF curr_bid; 34 35 BEGIN 36 /* 37 || lock row in auction_items 38 */ 39 OPEN auction_item_cur; 40 FETCH auction_item_cur INTO temp_curr_bid, temp_status; 41 42 /* 43 || do some validity checks 44 */ 45 IF auction_item_cur%NOTFOUND 46 THEN 47 RAISE invalid_item; 48 49 ELSIF temp_status = 'CLOSED' 50 THEN 51 RAISE item_is_closed; 52 53 ELSIF bid_IN <= temp_curr_bid 54 THEN 55 RAISE bid_too_low; 56 57 ELSE 58 /* 59 || insert to bids AND update auction_items, 60 || bidders identified by session username 61 */ 62 INSERT INTO bids (bidder, item_id, bid) 63 VALUES (USER, item_id_IN, bid_IN); 64 65 UPDATE auction_items 66 SET curr_bid = bid_IN 67 WHERE CURRENT OF auction_item_cur; 68 69 /* 70 || commit is important because it will send 71 || the alert notifications out on the item 72 */ 73 COMMIT; 74 75 /* 76 || register for alerts on item since bidding, 77 || register after commit to avoid ORU-10002 78 */ 79 DBMS_ALERT.REGISTER(item_id_IN); 80 81 END IF; 82 83 CLOSE auction_item_cur; 84 85 EXCEPTION 86 WHEN invalid_item 87 THEN 88 ROLLBACK WORK; 89 RAISE_APPLICATION_ERROR 90 (-20002,'PLACE_BID ERR: invalid item'); 91 WHEN bid_too_low 92 THEN 93 ROLLBACK WORK; 94 RAISE_APPLICATION_ERROR 95 (-20003,'PLACE_BID ERR: bid too low'); 96 WHEN item_is_closed 97 THEN 98 ROLLBACK WORK; 99 RAISE_APPLICATION_ERROR 100 (-20004,'PLACE_BID ERR: item is closed'); 101 WHEN OTHERS 102 THEN 103 ROLLBACK WORK; 104 RAISE; 105 END place_bid; 106 107 PROCEDURE close_item(item_id_IN IN VARCHAR2) 108 IS 109 BEGIN 110 UPDATE auction_items 111 SET status = 'CLOSED' 112 WHERE id = item_id_IN; 113 114 /* commit to raise alert */ 115 COMMIT WORK; 116 END close_item; 117 118 PROCEDURE watch_bidding(timeout_secs_IN IN NUMBER:=300) 119 IS 120 temp_name VARCHAR2(30); 121 temp_message VARCHAR2(1800); 122 temp_status INTEGER; 123 BEGIN 124 /* 125 || enter a loop which will be exited explicitly 126 || when a new bid from another user received or 127 || DBMS_ALERT.WAITANY call times out 128 */ 129 LOOP 130 /* 131 || wait for up to 10 minutes for any alert 132 */ 133 DBMS_ALERT.WAITANY 134 (temp_name, temp_message, temp_status, timeout_secs_IN); 135 136 IF temp_status = 1 137 THEN 138 /* 139 || timed out, return control to application 140 || so it can do something here if necessary 141 */ 142 EXIT; 143 144 ELSIF temp_message = 'CLOSED' 145 THEN 146 /* 147 || unregister closed item, re-enter loop 148 */ 149 DBMS_ALERT.REMOVE(temp_name); 150 DBMS_OUTPUT.PUT_LINE('Item '||temp_name|| 151 ' has been closed.'); 152 153 ELSIF temp_message = USER OR temp_message = 'OPEN' 154 THEN 155 /* 156 || bid was posted by this user (no need to alert) 157 || re-enter loop and wait for another 158 */ 159 NULL; 160 161 ELSE 162 /* 163 || someone raised the bid on an item this user is bidding 164 || on, application should refresh user's display with a 165 || query on the high_bids view and/or alert visually 166 || (we will just display a message) 167 || 168 || exit loop and return control to user so they can bid 169 */ 170 DBMS_OUTPUT.PUT_LINE 171 ('Item '||temp_name||' has new bid: '|| 172 TO_CHAR(curr_bid(temp_name),'$999,999.00')|| 173 ' placed by: '||temp_message); 174 EXIT; 175 END IF; 176 END LOOP; 177 178 END watch_bidding; 179 180 END auction; 181 /

Package body created.

SQL> SQL> --End auction1.sql SQL> --Auction2.sql SQL> SQL> CREATE OR REPLACE TRIGGER auction_items_ARU 2 AFTER UPDATE ON auction_items 3 FOR EACH ROW 4 BEGIN 5 /* 6 || trigger enforces no update of item_id and also 7 || signals an alert when status changes 8 */ --Supply missing code. 18 END auction_items_ARU; 19 /

Trigger created.

SQL> SQL> CREATE OR REPLACE TRIGGER bids_ARIUD 2 AFTER INSERT OR UPDATE OR DELETE ON bids 3 FOR EACH ROW 4 BEGIN 5 /* 6 || enforce all bids are final rule 7 */ 8 --Supply missing code. 18 19 END bids_ARIUD; 20 /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER auction_items_ARU 2 AFTER UPDATE ON auction_items 3 FOR EACH ROW 4 BEGIN 5 /* 6 || trigger enforces no update of item_id and also 7 || signals an alert when status changes 8 */ 9 --Supply missing code. 18 END auction_items_ARU; 19 /

Trigger created.

SQL> INSERT INTO auction_items 2 VALUES ('GB123','Antique gold bracelet',350.00,NULL,'OPEN');

1 row created.

SQL> SQL> INSERT INTO auction_items 2 VALUES ('PS447','Paul Stankard paperweight',550.00,NULL,'OPEN');

1 row created.

SQL> SQL> INSERT INTO auction_items 2 VALUES ('SC993','Schimmel print',750.00,NULL,'OPEN');

1 row created.

SQL> SQL> COMMIT;

FIRST BIDDER.TXT

SQL> set serveroutput on size 100000 SQL> set verify on SQL> BEGIN 2 xyz.auction.place_bid('GB123',&bid); 3 xyz.auction.watch_bidding(15); 4 END; 5 / Enter value for bid: 200 old 2: xyz.auction.place_bid('GB123',&bid); new 2: xyz.auction.place_bid('GB123',200); xyz.auction.place_bid('GB123',200); * ERROR at line 2: ORA-06550: line 2, column 4: PLS-00201: identifier 'XYZ.AUCTION' must be declared ORA-06550: line 2, column 4: PL/SQL: Statement ignored ORA-06550: line 3, column 4: PLS-00201: identifier 'XYZ.AUCTION' must be declared ORA-06550: line 3, column 4: PL/SQL: Statement ignored

SQL> set serveroutput on size 100000 SQL> set verify on SQL> BEGIN 2 xyz.auction.place_bid('GB123',&bid); 3 xyz.auction.watch_bidding(15); 4 END; 5 / Enter value for bid: 500 old 2: xyz.auction.place_bid('GB123',&bid); new 2: xyz.auction.place_bid('GB123',500); Item GB123 has new bid: $600.00 placed by: X2