Untitled

PROGRAM: 08
Implement Locks for particular table.
Step 1: Login to admin user USERNAME:system PASSWORD:student/system
SQL> connect
Enter user-name: system
Enter password:
Connected.
Step 2:Create a table device8 and insert 5 records.
SQL> create table device8
 2 (
 3 did number(5) primary key,
 4 dname varchar(20),
 5 cost number(5));
Table created.
SQL> insert into device8 values(1,'printer',6000);
SQL> insert into device8 values(2,'mouse',1000);
SQL> insert into device8 values(3,'keyboard',8000);
SQL> insert into device8 values(4,'monitor',5000);
SQL> insert into device8 values(5,'pendrive',500);
SQL> select * from device8;
 DID DNAME COST
---------- -------------------- ----------
 1 printer 6000
 2 mouse 1000
 3 keyboard 8000
 4 monitor 5000
 5 pendrive 500
SQL>commit;
Step 3:Execuate the following query.
SQL> update device8 set cost=800 where did=5;
OUTPUT:
1 row updated.

SQL> set autocommit off;
SQL> lock table device8 in exclusive mode nowait;
OUTPUT:
Table(s) Locked.
Step 4: Create a new user USERNAME:sem3 PASSWORD dbms.
SQL> create user sem3 identified by dbms;
OUTPUT:
User created.
Step 5: Grant permission to new user sem3.
SQL> grant connect,resource,dba to sem3;
OUTPUT:
Grant succeeded.
Step 6:Now open new window(command prompt) login to new user you have created.
SQL> connect
Enter user-name: sem3
Enter password:
Connected.
SQL> select * from system.device8;
 DID DNAME COST
---------- -------------------- ----------
 1 printer 6000
 2 mouse 1000
 3 keyboard 8000
 4 monitor 5000
 5 pendrive 800
Step 7: Execute the following query.
SQL> update system.device8 set cost=200 where did=5;
OUTPUT:
1 row updated

Step 8: Go to Admin user & try to update the record.
SQL> update device8 set cost=100 where did=5;
|
This happens because the admin user has locked the table device8.
Step 9:Go to Sem3 user & type commit.(When you enter commit in sem3 user automatically 
record will be updated in admin user).
SQL> commit;
Commit complete.







PROGRAM: 09

Write a PL/SQL procedure for an application using exception 

handling.
CREATE TABLE CUST
(
EID NUMBER(5) PRIMARY KEY,
NAME VARCHAR(20),
ADDRESS VARCHAR(20),
SALARY NUMBER(5));
INSERT INTO CUST VALUES(1,'RAM','BELGAUM',20000);
INSERT INTO CUST VALUES(2,'VIVEK','HUBALI',20000);
INSERT INTO CUST VALUES(3,'RAMAN','GOA',20000);
INSERT INTO CUST VALUES(4,'SOUMYA','DHARWAD',20000);
SELECT * FROM CUST;
DECLARE 
c_id cust.eid%type := 8; 
c_name cust.name%type; 
c_addr cust.address%type; 
BEGIN 
SELECT name, address INTO c_name, c_addr 
FROM cust 
WHERE eid = c_id; 
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name); 
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
EXCEPTION 
WHEN no_data_found THEN 
dbms_output.put_line('No such customer!'); 
WHEN others THEN 
dbms_output.put_line('Error!'); 
END;