Home » SQL & PL/SQL » SQL & PL/SQL » would like to double check .....
would like to double check ..... [message #200911] Wed, 01 November 2006 19:57 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
hi everyone, i just want to make sure that my code meet the requirement on the question .. can someone help me please....

requirement is:

PROCEDURE end_campaign(ctitle IN campaign.title%Type)

for a given campaign, this procedure calcculates the actual cost of the campaign from the actual cosxts of the adverts for that campaign, and it creates an invoice for the campaign with DateIssued = sysdate and DatePaid = null. it uses the invoice_number sequece to assign a fresh and unique invoice number. if the given campaign does not exist, it deals with the corresponding no_data_found exception. the procedure body musty end with a commit statement in case of successful completion(as the procedure will update the database tables.)

this is my code


	PROCEDURE end_campaign(ctitle IN campaign.title%Type)
	IS
		CURSOR end_campaign_cur 
		IS
			SELECT campaigntitle, SUM(actualcost)
			FROM advert
			group by campaigntitle;
	BEGIN
		FOR end_campaign_rec IN end_campaign_cur LOOP
			IF ctitle = end_campaign_rec.campaigntitle THEN
				INSERT INTO invoice (invoiceno, campaigntitle, dateissued, datepaid, status)
					VALUES (invoice_invoiceno_seq.nextval, ctitle, SYSDATE, NULL, ' ');
			END IF;
		END LOOP;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE('NO SUCH TITLE FOUND');
	COMMIT;
	END end_campaign;


please add some code.. thank you... and also explaation will be appreciated.
Re: would like to double check ..... [message #200984 is a reply to message #200911] Thu, 02 November 2006 02:22 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess you have sample data, don't you? So why wouldn't you run this procedure and figure out is it correctly written or not?
Re: would like to double check ..... [message #201001 is a reply to message #200984] Thu, 02 November 2006 03:52 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
the format is correct but i dont know whether the code will do wat is required....

Re: would like to double check ..... [message #201006 is a reply to message #201001] Thu, 02 November 2006 04:08 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Test it, and you'll know.
Re: would like to double check ..... [message #201016 is a reply to message #201006] Thu, 02 November 2006 04:29 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
the problem is i dont know how to test the program....sorry.. would you mind?
Re: would like to double check ..... [message #201020 is a reply to message #201016] Thu, 02 November 2006 04:39 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a test case. It is a set of known input data and expected output data. Write that in a form of a table using any text processor (Word, Notepad, ...).

Execute your procedure for every input and confirm that it is the same as expected output. If it is, then the procedure works correctly. If not, think about it and fix what is wrong.

Procedure (the one you have here) accepts one IN parameter: "ctitle". Find those "ctitles" and run the procedure using

SQL> execute end_campaign(first_ctitle);
SQL> execute end_campaign(second_ctitle);
etc.
Re: would like to double check ..... [message #201027 is a reply to message #201020] Thu, 02 November 2006 04:53 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
can is just insert like this

execute end_campaign(&title);
Re: would like to double check ..... [message #201028 is a reply to message #201027] Thu, 02 November 2006 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try and you'll see. Or, go to Tahiti.
Re: would like to double check ..... [message #201029 is a reply to message #201027] Thu, 02 November 2006 05:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Two more test cases.

a) As you are trying to commit your changes.

1. Exit from the session.
2. Connect again and check whether your changes are reflected or not.

b) As you want to display a message if the entered one is not present.
1. Cross check by passing some value, which is not there in the table.

By
Vamsi

[Updated on: Thu, 02 November 2006 05:08]

Report message to a moderator

Re: would like to double check ..... [message #201032 is a reply to message #200911] Thu, 02 November 2006 05:13 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
yap it works.. but for the status it is NULL as i assign it as

the status in the invoice table is default = issued

CREATE TABLE Invoice
(InvoiceNo NUMBER (5),
CampaignTitle VARCHAR2 (30),
DateIssued DATE,
DatePaid DATE,
Status CHAR(10) DEFAULT 'Issued',
CONSTRAINT Invoice_InvoiceNo_pk PRIMARY KEY(InvoiceNo),
CONSTRAINT Invoice_CampaignTitle_fk FOREIGN KEY(Campaigntitle)REFERENCES campaign(title));


and


	INSERT INTO invoice (invoiceno, campaigntitle, dateissued, datepaid, status)
					VALUES (invoice_invoiceno_seq.nextval, ctitle, SYSDATE, NULL, ' ');



did i done something wrong?
Re: would like to double check ..... [message #201035 is a reply to message #201032] Thu, 02 November 2006 05:28 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Seems this thread will go forever.

I feel better to point out some.

1. The default value will get inserted when you pass either nothing or DEFAULT only. If you pass something like NULL, ' ' etc it will insert them.
So, you need to do the following.
a) don't pass status at all
or
b) pass DEFAULT

2. COMMIT will not be executed after insertion as it is in the exception section. In your test case it has worked because you might have executed and 'exit' statement in sql* plus. (That is an implicit commit). If you close the sql* plus window then the changes won't be reflected.

3. Cursor will not raise NO_DATA_FOUND error even though the select fetches nothing. You need to check the count or assign something to a dummy variable in the loop and checking it outside is a solution for that.

Please visit Oracle Documentation site once.

By
Vamsi
Re: would like to double check ..... [message #201038 is a reply to message #201035] Thu, 02 November 2006 05:44 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
vamsi kasina wrote on Thu, 02 November 2006 05:28
Seems this thread will go forever.

I feel better to point out some.

1. The default value will get inserted when you pass either nothing or DEFAULT only. If you pass something like NULL, ' ' etc it will insert them.
So, you need to do the following.
a) don't pass status at all
or
b) pass DEFAULT

2. COMMIT will not be executed after insertion as it is in the exception section. In your test case it has worked because you might have executed and 'exit' statement in sql* plus. (That is an implicit commit). If you close the sql* plus window then the changes won't be reflected.

3. Cursor will not raise NO_DATA_FOUND error even though the select fetches nothing. You need to check the count or assign something to a dummy variable in the loop and checking it outside is a solution for that.

Please visit Oracle Documentation site once.

By
Vamsi



what you mean is put commit before exception??

and what you mean for third point bout raise no_data_found??
Re: would like to double check ..... [message #201041 is a reply to message #201038] Thu, 02 November 2006 05:54 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Tahiti is right here.

./fa/1678/0/
  • Attachment: tahiti.gif
    (Size: 7.25KB, Downloaded 339 times)
Re: would like to double check ..... [message #201046 is a reply to message #200911] Thu, 02 November 2006 06:18 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
huh,, tat is not related dude...
Re: would like to double check ..... [message #201048 is a reply to message #200911] Thu, 02 November 2006 06:19 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
can you please let me know how could i raise the NO_DATA_FOUND
Re: would like to double check ..... [message #201049 is a reply to message #201048] Thu, 02 November 2006 06:26 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is not related? Tahiti? But you'll find a fine "PL/SQL User's Guide and Reference" in a hut on the beach! I'm sure of it, only if you'd click on the link.

What do you mean by raising NO-DATA-FOUND? Writing a query which will end up with this error, or handling it?
Re: would like to double check ..... [message #201050 is a reply to message #201048] Thu, 02 November 2006 06:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

vamsi:
assign something to a dummy variable in the loop and checking it outside is a solution for that.



I quit Bored

By
Vamsi
Re: would like to double check ..... [message #201051 is a reply to message #200911] Thu, 02 November 2006 06:35 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
well ... as you said, when i entered the data tat is not in the table and the exception will not call .. dont know why ..
please let me know what is the problem please
Re: would like to double check ..... [message #201054 is a reply to message #201051] Thu, 02 November 2006 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Because it is NOT an exception. Cursor FOR loops opens a cursor, fetches records from it and finds out that there's nothing to return, so it just closes the cursor and finishes your PL/SQL procedure. It was just as if you had EXIT WHEN cursor%NOTFOUND which happened immediatelly after fetch.

Here's a dummy block which will raise the NO-DATA-FOUND:
SQL> l
  1  declare x number;
  2  begin
  3    select 1 into x from dual
  4    where 1 = 2;
  5  exception
  6    when no_data_found then
  7      raise_application_error(-20101, 'I didn''t find anything');
  8* end;
SQL> /
declare x number;
*
ERROR at line 1:
ORA-20101: I didn't find anything
ORA-06512: at line 7

SQL>
Re: would like to double check ..... [message #201061 is a reply to message #200911] Thu, 02 November 2006 07:11 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
so you mean that no_data_found is not exception type ??
Re: would like to double check ..... [message #201069 is a reply to message #201061] Thu, 02 November 2006 07:22 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

vamsi:
Cursor WILL NOT raise NO_DATA_FOUND error, even though the select fetches nothing. This is expected behaviour.



Can you PLEASE read the documentation regarding cursors, if you have any question?

By
Vamsi
Re: would like to double check ..... [message #201071 is a reply to message #200911] Thu, 02 November 2006 07:27 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
so i shouldnt be using cursor ..
Re: would like to double check ..... [message #201078 is a reply to message #201071] Thu, 02 November 2006 07:48 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What you should do is go to Tahiti.
Re: would like to double check ..... [message #201080 is a reply to message #201078] Thu, 02 November 2006 07:53 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
here is my new code

PROCEDURE end_campaign(ctitle IN campaign.title%Type)
	IS
		v_actualcost advert.actualcost%Type;
		v_campaigntitle advert.campaigntitle%Type;
	BEGIN
		SELECT campaigntitle, SUM(actualcost) Total
		INTO v_campaigntitle, v_actualcost
		FROM advert
		GROUP BY campaigntitle;
		
		IF ctitle = v_campaigntitle THEN
			INSERT INTO invoice (invoiceno, campaigntitle, dateissued, datepaid, status)
				VALUES (invoice_invoiceno_seq.nextval, ctitle, SYSDATE, NULL, DEFAULT );
			UPDATE campaign 
				SET datepaid = SYSDATE 
				WHERE title = ctitle;
			UPDATE campaign
				SET actualcost = v_actualcost
				WHERE title = ctitle;
		END IF;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE(' Invalid Title ');
	COMMIT;
	END end_campaign;


but still got error..

BEGIN end_campaign('CDC'); END;

*

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "S4106388.END_CAMPAIGN", line 6
ORA-06512: at line 1
Re: would like to double check ..... [message #201087 is a reply to message #201080] Thu, 02 November 2006 08:23 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
ya it says it returns more number of rows than expected so use some filter in the group by clause using the having clause

and by the way

tahiti is

tahiti.oracle.com
Re: would like to double check ..... [message #201096 is a reply to message #201080] Thu, 02 November 2006 08:52 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I don't know, maybe it's just me, but this reminds me of:
http://www.baseball-almanac.com/humor4.shtml
Re: would like to double check ..... [message #201126 is a reply to message #201096] Thu, 02 November 2006 12:25 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Uh oh, so true ./fa/917/0/
Re: would like to double check ..... [message #201206 is a reply to message #200911] Fri, 03 November 2006 01:24 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
just want to say tha the exception still not being called when i entered invalid data. here is my edited code


	PROCEDURE end_campaign(ctitle IN campaign.title%Type)
	IS
		v_actualcost advert.actualcost%Type;
		v_campaigntitle advert.campaigntitle%Type;
	BEGIN
		SELECT campaigntitle, SUM(actualcost) Total
		INTO v_campaigntitle, v_actualcost
		FROM advert
		HAVING campaigntitle = ctitle
		GROUP BY campaigntitle;
		
		IF ctitle = v_campaigntitle THEN
			INSERT INTO invoice (invoiceno, campaigntitle, dateissued, datepaid, status)
				VALUES (invoice_invoiceno_seq.nextval, ctitle, SYSDATE, NULL, DEFAULT );
			UPDATE campaign 
				SET datepaid = SYSDATE 
				WHERE title = ctitle;
			UPDATE campaign
				SET actualcost = v_actualcost
				WHERE title = ctitle;
		END IF;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			DBMS_OUTPUT.PUT_LINE(' Invalid Title ');
	COMMIT;
	END end_campaign;


can anyone let me know please???
Re: would like to double check ..... [message #201211 is a reply to message #201206] Fri, 03 November 2006 01:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Can you please cross check whether serveroutput is on or off?

By
Vamsi
Re: would like to double check ..... [message #201213 is a reply to message #200911] Fri, 03 November 2006 01:52 Go to previous message
hiip
Messages: 78
Registered: October 2006
Member
how could i forget that server output .. thank you bro...you are genuise
Previous Topic: Procedure Syntax
Next Topic: How do i set default database/user for statements?
Goto Forum:
  


Current Time: Sat Dec 03 01:20:34 CST 2016

Total time taken to generate the page: 0.14663 seconds