Home » SQL & PL/SQL » SQL & PL/SQL » commit or rollback
commit or rollback [message #301876] Fri, 22 February 2008 02:19 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
how can i rollback else commit
in my script
my requirement is
when id is not found or cannot updated(exception
WHEN OTHERS THEN
rollback;
i want to roolback and drop column
else commit

sample code:

set serveroutput on
declare
v_count number;
V_std_ID NUMBER(9) := 333;
begin
alter table std_sheet add (std_rep_data varchar (15));
select count(1) into v_count from std_sheet where FK_std_id = V_std_ID;
if v_count > 0 then
UPDATE std_rep rp
SET std_rep_data = (
  SELECT std_marks
  FROM std_sheet ss
  WHERE rp.pK_std_id=ss.fk_std_id
and ss.FK_std_id=333);
else dbms_output.put_line('NOTE:???????????? Pls Enter valid student_ID this student_ID does not exists');
end if;
exception
WHEN OTHERS THEN
rollback;
alter table std_sheet drop (std_rep_data varchar (15));
end;
else
commit;
dbms_output.put_line('NOTE!!!!!!!!! table std_sheet altered and transaction committed');
end;
/
Re: commit or rollback [message #301879 is a reply to message #301876] Fri, 22 February 2008 02:41 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, adding a column during runtime is a bad idea. Why would you want to do that? Let it exist all the time! (delete its contents if you have to).

Besides, ALTER TABLE wouldn't work as you'd want it to; you'd need to use dynamic SQL.

Rollback isn't necessary - just raise_application_error.

P.S. Forgot to mention: please, format your code. Using [code] tags does NOT mean it is formatted.

What's ELSE doing in the EXCEPTION handling section? I guess you wanted to use another BEGIN-EXCEPTION-END block within the IF-THEN-ELSE (but that's only my point of view).

[Updated on: Fri, 22 February 2008 02:43]

Report message to a moderator

Previous Topic: Encryption (merged)
Next Topic: error in executing the package
Goto Forum:
  


Current Time: Thu Dec 08 10:19:51 CST 2016

Total time taken to generate the page: 0.10815 seconds