Answer Posted / tvgiriprasad

If you want to UPDATE or DELETE record from table by using cursor which is defined from same table,"WHERE CURRENT OF" clause can be used. We have to create cursor with "FOR UPDATE" clause to use above clause.

The most recent record fetched from the table (by looping of cursor records) should be updated or deleted by using "WHERE CURRENT OF". When a cursor open with FOR UPDATE clause ,cursor's active set will have row level Exclusive Lock. Other sessions can query the table records, and not able to delete/update or SELECT with FOR UPDATE clause.


Example: Want to update value for GRADE column to '1' for the student who has Id = '1'.

Declare
cursor C1 is
select st_id, grade,st_last_name from student where st_id = 3;
for update of grade;
[ variable declaration ……] ;
begin
open C1;
fetch C1 into v_id,v_grade,v_name;
if C1%notfound then
dbms_output.put_line (‘No Record is found.’);
else
update student set grade=1 WHERE CURRENT OF;
COMMIT;
END IF;

CLOSE C1;
end;

nw i'm doing MBA system and planning to do oracle,database management.tell me that whether it is useful r not?.refer any course for my carrier pls

2145

---

How to run create database statement?

975

---

What is dynamic proxy?

959

---

How many categories of data types in oracle?

953

---

> CREATE OR REPLACE FUNCTION FACTORIAL_1(factstr varchar2 ) 2 RETURN NUMBER AS 3 new_str VARCHAR2(4000) := factstr||'*' ; 4 fact number := 1 ; 5 BEGIN 6 7 WHILE new_str IS NOT NULL 8 LOOP 9 fact := fact * TO_NUMBER(SUBSTR(new_str,1,INSTR(new_str,'*')-1)); 10 new_str := substr( new_str,INSTR(new_str,'*')+1); 11 END LOOP; 12 13 RETURN fact; 14 15 END; explanation Above program?

1980

---

What is the quickest way to fetch the data from a table?

1000

---

How to empty your oracle recycle bin?

980

---

From the following identify the non schema object: packages, triggers, public synonyms, tables and indexes.

1010

---

What are named parameters?

1033

---

can u send the sql dumps to sivakumarr1987@gmail.com plz help me

2995

---

What is a proxy object?

1008

---

What are the differences between char and varchar2 in oracle?

1072

---

Is there an oracle sql query that aggregates multiple rows into one row?

931

---

What is a directory object?

1023

---

What do you mean by a tablespace?

980

---