Home » SQL & PL/SQL » SQL & PL/SQL » Reg. cursor based delete! Little Urgent! (Oracle 9i)
Reg. cursor based delete! Little Urgent! [message #318499] Wed, 07 May 2008 01:00 Go to next message
Guru01
Messages: 4
Registered: May 2008
Junior Member
Hi all,

While accessing for a change now, i need to delete the values that came out of the cursor.
For an Exting functionality: I am fetching few values by a cursor and i am starting a loop for executing few condtions based on the records available in the cursor. there after i am explicitly closing the cursor. The issue i have now is, since i am using the loop the pointer would be in some row x or y or z after executing the loop. Now, my question is, If i delete by using the where current of clause by giving the cursor will all the rows that came from the cursor be deleted? Before Planning the above way, i am moving the close cursor statement towards the end of my Function.

The existing functional code is like below:

CURSOR ship_unit_det_cur IS SELECT CNTNR_I
FROM SHIP_UNIT_DET
WHERE SHIP_UNIT_I = prm_cnt_i;
.
--Some set of statements--
......
BEGIN

OPEN ship_unit_det_cur;
FETCH ship_unit_det_cur INTO var_cnt_i;

if ship_unit_det_cur%NOTFOUND then /* if empty */

if(prm_log_err = 'Y') then
err_ret_code2 := dsh_insert_drvt_evnt (prm_cnt_i, 'SHP', 'DVRT',
SYSDATE, USER, prm_hi_lvl_strg_i,
NULL, prm_load_grp_i, prm_dvrt_mthd_i, 8109);
end if;

commit;
return 8109;
end if;

LOOP

FETCH ship_unit_det_cur INTO var_cnt_i;
EXIT WHEN ship_unit_det_cur%NOTFOUND;

stproc_loc := 'dsh_load 2';

err_ret_code := dsh_load_cntnrs_pkg.dsh_load (var_cnt_i,
var_prm_cnt_par_i,
prm_load_grp_i,
prm_hi_lvl_strg_i,
prm_dvrt_mthd_i,
prm_store_door_assn_i,
dummy,
prm_pgm_n,
'Y',
NULL);

if(err_ret_code = 8999) then /* on system error, always quit */
stproc_loc := 'LoadDet 2';
errdesc := prm_cnt_i || ' Failed fo load detail for ' || var_cnt_i;
RAISE SYSTEM_ERROR;
end if;

END LOOP;

CLOSE ship_unit_det_cur;

END;

--Some set of statements--
......

--Now delete the shipping unit information--

BEGIN

stproc_loc := 'DletShipUnit';
savepoint DELETE_SHIP_UNIT;

delete from ship_unit_det where ship_unit_i = prm_cnt_i;

if(prm_reusbl_f = 'N') then -- non-reusable master
delete from shipping_unit where ship_unit_i = prm_cnt_i;
end if;

EXCEPTION

WHEN OTHERS THEN
rollback to DELETE_SHIP_UNIT;
stproc_loc := 'DelShUn';
errdesc := prm_cnt_i || ' Failed to delete shipping unit';
RAISE SYSTEM_ERROR;
END;

In the above Begin and End Block, For the new proposed change functional code, i need to delete based on cursor. Hence forth i am planning to add the for update clause in my Cursor statement to lock the cursor values till i execute the delete.

And in delete i am using the where current of clause. Now the question is where do i put the commit? As you can see in the above block, after i replace the delete by following,

-- delete from ship_unit_det where ship_unit_i = prm_cnt_i;
delete from ship_unit_det where current of ship_unit_det_cur;

to unlock the cursor i need to place a commit. If some operation fail in the if condition below the proposed change, the rollback to the save point need to happen! so, can i place the commit in the place below Exception and end statement like:

EXCEPTION

WHEN OTHERS THEN
rollback to DELETE_SHIP_UNIT;
stproc_loc := 'DelShUn';
errdesc := prm_cnt_i || ' Failed to delete shipping unit';
RAISE SYSTEM_ERROR;

COMMIT;
CLOSE ship_unit_det_cur;
END;

Or Is there any other way to process the same?
Re: Reg. cursor based delete! Little Urgent! [message #318515 is a reply to message #318499] Wed, 07 May 2008 01:32 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi,
please use code tags on your post as per the guidelines Also, please avoid using the Urgent in your posts (also per the guidelines)
Re: Reg. cursor based delete! Little Urgent! [message #318571 is a reply to message #318499] Wed, 07 May 2008 03:29 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
don't you get get fetch out of sequence or
ora -01002
yours
dr.s.raghunathan
Previous Topic: Regarding IN Operator
Next Topic: File write error
Goto Forum:
  


Current Time: Wed Dec 07 14:29:11 CST 2016

Total time taken to generate the page: 0.11244 seconds