Dear All,
In my package (Existing one in production) same cursor is opened and closed so many times. What is the performace benefit or impact of this method? what are all the process oracle will do while open and close a cursor?
As per my personal opinion,instead of open and close same cursor multiple time,usage of collection is better. Am i correct?
[code]
DECLARE
v_account_no VARCHAR2(20);
CURSOR c1
IS
SELECT account_no FROM test_table WHERE account_id=121321;
BEGIN
OPEN c1;
FETCH c1 INTO v_account_no;
-- doing some process
CLOSE c1;
END;
BEGIN
OPEN c1;
FETCH c1 INTO v_account_no;
-- doing some process
CLOSE c1;
END;
[code]
instead of above code can i use below code for performace improvement?
[code]
DECLARE
v_account_no VARCHAR2(20);
type v_account_type
IS
TABLE OF test_table.account_no%type;
v_account typ v_account_type;
BEGIN
SELECT account_no bulk collect
INTO v_account typ
FROM test_table
WHERE account_id=121321;
FOR i IN 1..v_account typ.count -- In a single loop we can do all the process
LOOP
BEGIN
-- do all the process
END;
BEGIN
-- do all the process
END;
END LOOP;
END;
[code]
Thanks ,
S.Balraj