Home » SQL & PL/SQL » SQL & PL/SQL » delete but still displayed in the table
delete but still displayed in the table [message #201261] Fri, 03 November 2006 05:44 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
hi everyone, this is what i done but nothing is deleted from my table and when i compile it .. it says success...


		PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		v_datepaid campaign.datepaid%TYPE;
		v_campaignfinishdate campaign.campaignfinishdate%Type;
		v_title campaign.title%Type;
		campaign_not_finished EXCEPTION;
	BEGIN
		SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE v_title = ctitle;
		
		DELETE FROM advert
			WHERE v_title= campaigntitle;
			
		DELETE FROM staffcampaign
			WHERE v_title = title;
			
		DELETE FROM campaign
			WHERE v_title = ctitle;

		IF v_datepaid IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
		IF v_campaignfinishdate IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
	EXCEPTION 
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE
			('Invalid campaign title');
		WHEN campaign_not_finished THEN
			DBMS_OUTPUT.PUT_LINE
			('Campaign not finished');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
	COMMIT;
	END delete_campaign;


when i compile it and test the code , this is the solution displayed...

PL/SQL procedure successfully completed.

but when i check on the table, such as select * from advert.. it did not delete anything at all....

can someone let me know what is the problem/???
Re: delete but still displayed in the table [message #201264 is a reply to message #201261] Fri, 03 November 2006 05:56 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
To debug it, try display the sql%rowcount after every statement:

SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE v_title = ctitle;
		
dbms_output.put_line('Rows returned - '||sql%rowcount);

		DELETE FROM advert
			WHERE v_title= campaigntitle;

dbms_output.put_line('Rows deleted delete 1 - '||sql%rowcount);

...ETC
Re: delete but still displayed in the table [message #201265 is a reply to message #201264] Fri, 03 November 2006 06:04 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
i have tried the procedure again

	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		v_datepaid campaign.datepaid%TYPE;
		v_campaignfinishdate campaign.campaignfinishdate%Type;
		v_title campaign.title%Type;
		campaign_not_finished EXCEPTION;
	BEGIN
		SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE v_title = ctitle;
		
		dbms_output.put_line('Rows returned - '||sql%rowcount);
		DELETE FROM advert
			WHERE v_title = ctitle;
		dbms_output.put_line('Rows deleted delete 1 - '||sql%rowcount);

		IF v_datepaid IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
		IF v_campaignfinishdate IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
	EXCEPTION 
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE
			('Invalid campaign title');
		WHEN campaign_not_finished THEN
			DBMS_OUTPUT.PUT_LINE
			('Campaign not finished');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
	COMMIT;
	END delete_campaign;



and the message is

Invalid campaign title
PL/SQL procedure successfully completed.

but te data in the table is not deleted.. ...
so frustrated,,,,
Re: delete but still displayed in the table [message #201266 is a reply to message #201261] Fri, 03 November 2006 06:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
It is similar to your other post

commit before exception

The statements in the EXCEPTION block will get executed only when an exception has been raised and that too for the particular WHEN clause.

In your case, the commit will get executed when it goes to when others section.

		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
	COMMIT;


I hope you have understood where to put the COMMIT statement.


By
Vamsi.

[Updated on: Fri, 03 November 2006 06:06]

Report message to a moderator

Re: delete but still displayed in the table [message #201267 is a reply to message #201265] Fri, 03 November 2006 06:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It means that this query

SELECT title, datepaid, campaignfinishdate
INTO v_title, v_datepaid, v_campaignfinishdate
FROM campaign
WHERE v_title = ctitle;

returned nothing and raised NO-DATA-FOUND exception.
Re: delete but still displayed in the table [message #201271 is a reply to message #201266] Fri, 03 November 2006 06:21 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
the commit statement should put under the delete clause.. am i right..?? if it is .. the error comes out with invalid campaign title.. but the title is in the table..
Re: delete but still displayed in the table [message #201272 is a reply to message #201271] Fri, 03 November 2006 06:24 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Cross check your data once again by running it.

SELECT title, datepaid, campaignfinishdate
  FROM campaign
 WHERE v_title = '&title';


By
Vamsi
Re: delete but still displayed in the table [message #201273 is a reply to message #201261] Fri, 03 November 2006 06:25 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The title has to match exactly - right letter case, correct number of spaces between words and correct number of trailing spaces, if any. If there is even the smallest difference, you will not get a match.
Re: delete but still displayed in the table [message #201276 is a reply to message #201261] Fri, 03 November 2006 06:27 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
this is the mod code

	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		v_datepaid campaign.datepaid%TYPE;
		v_campaignfinishdate campaign.campaignfinishdate%Type;
		v_title campaign.title%Type;
		campaign_not_finished EXCEPTION;
	BEGIN
		SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE title = ctitle;
		
		dbms_output.put_line('Rows returned - '||sql%rowcount);
		DELETE FROM advert
			WHERE v_title = ctitle;
		COMMIT;
		dbms_output.put_line('Rows deleted delete 1 - '||sql%rowcount);

		IF v_datepaid IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
		IF v_campaignfinishdate IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
	EXCEPTION 
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE
			('Invalid campaign title');
		WHEN campaign_not_finished THEN
			DBMS_OUTPUT.PUT_LINE
			('Campaign not finished');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
		END delete_campaign;


when i compile this.. it is successful compiled but it delete whole my table....

and this is wat it displayed

Rows returned - 1
Rows deleted delete 1 - 0
PL/SQL procedure successfully completed.
Re: delete but still displayed in the table [message #201277 is a reply to message #201261] Fri, 03 November 2006 06:31 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
this is the code i tested now


	PROCEDURE delete_campaign(ctitle IN campaign.title%Type)
	IS
		v_datepaid campaign.datepaid%TYPE;
		v_campaignfinishdate campaign.campaignfinishdate%Type;
		v_title campaign.title%Type;
		campaign_not_finished EXCEPTION;
	BEGIN
		SELECT title, datepaid, campaignfinishdate
		INTO v_title, v_datepaid, v_campaignfinishdate
		FROM campaign
		WHERE title = ctitle;
		
		dbms_output.put_line('Rows returned - '||sql%rowcount);
		DELETE FROM advert
			WHERE campaigntitle = ctitle;
		COMMIT;
		dbms_output.put_line('Rows deleted delete 1 - '||sql%rowcount);

		IF v_datepaid IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
		IF v_campaignfinishdate IS NULL THEN
			RAISE campaign_not_finished;
		END IF;
	EXCEPTION 
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE
			('Invalid campaign title');
		WHEN campaign_not_finished THEN
			DBMS_OUTPUT.PUT_LINE
			('Campaign not finished');
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE
			('It Fails for other reason');	
		END delete_campaign;



it works fine .. but please check for me .. thank you

and one more question is that if i have three delete statement, is that mean i need three commit statement ??
Re: delete but still displayed in the table [message #201279 is a reply to message #201261] Fri, 03 November 2006 06:34 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
No, you only need one. You can choose to commit or rollback all three deletes as part of the same transaction.
Re: delete but still displayed in the table [message #201282 is a reply to message #201276] Fri, 03 November 2006 06:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Also while debugging, use sql%rowcount immediately after the DML statement.

otherwise it will not give expected results.

		DELETE FROM advert
			WHERE campaigntitle = ctitle;
		dbms_output.put_line('Rows deleted delete 1 - '||sql%rowcount);
		COMMIT;


By
Vamsi
Re: delete but still displayed in the table [message #201308 is a reply to message #201261] Fri, 03 November 2006 08:01 Go to previous message
hiip
Messages: 78
Registered: October 2006
Member
thank you guyz... you all give me lot of help.. i really appreciate that... you guyz rockz.. and geniuese
Previous Topic: Using WHEN OTHERS in Trigger
Next Topic: EXCEPTION Handling Stored Procedure
Goto Forum:
  


Current Time: Sat Dec 10 18:50:40 CST 2016

Total time taken to generate the page: 0.08616 seconds