PL/SQL Auction Program 1. Create a user xyz, who is the owner of the auction. Cr

ID: 3839841 • Letter: P

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 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