Home » SQL & PL/SQL » SQL & PL/SQL » urgent again.... please help..
urgent again.... please help.. [message #200210] Sun, 29 October 2006 05:25 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
help on this please

procedure end_campaign(ctitle in campaign.title%Type);

For a given campaign, this procedure calculates the actual cost of the campaign from the actual costs 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 sequence 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 must end with a commit statement in case of successful completion.

this is the relational data model
invoice(invoiceno, campaigntitle, dateissued, datepaid, status)
CampaignTitle_fk FOREIGN KEY(Campaigntitle)REFERENCES campaign(title)

status has a defaut value of 'issued'
invoiceno is sequence


here is my code

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


and my seq code is
------------------ CREATE SEQUENCE FOR INVOICENO --------------------
CREATE SEQUENCE invoice_invoiceno_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 99999
NOCACHE;
----------------- END OF SCRIPT --------------



and the error message are

LINE/COL ERROR
0/0 PL/SQL: Compilation unit analysis terminated
9/2 PL/SQL: Statement ignored
9/31 PLS-00225: subprogram or cursor 'END_CAMPAIGN_CUR' reference is o ut of scope
14/7 PLS-00201: identifier 'NO_DATA_FOND' must be declared
Re: urgent again.... please help.. [message #200213 is a reply to message #200210] Sun, 29 October 2006 05:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Read the manual on cursors. @Littlefoot suggested this in your earlier post - I suggest you do it.

To use a cursor, you must first OPEN it. You may do this with the OPEN statement, or it will be done for you in a cursor FOR-LOOP.

Also, check for spelling mistakes. The compiler is not FOND of them.

Ross Leishman
Re: urgent again.... please help.. [message #200216 is a reply to message #200213] Sun, 29 October 2006 06:44 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
yes i will print it out tomorrow as it is too long to just view it on the screen... anyway.. thnx ..
Re: urgent again.... please help.. [message #200307 is a reply to message #200210] Mon, 30 October 2006 05:33 Go to previous messageGo to next message
ajaybabu.yaleti
Messages: 11
Registered: October 2006
Junior Member
Hi,

You have to open the cursor and fetch the data from the table then only u can able to view the data.

or else

use for loop then there is no need to open, fetch and close as it does automatically.

Regards,
Ajay
Re: urgent again.... please help.. [message #200313 is a reply to message #200307] Mon, 30 October 2006 05:57 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ajaybabu.yaleti wrote on Mon, 30 October 2006 05:33

You have to open the cursor and fetch the data from the table then only u can able to view the data.

or else

use for loop then there is no need to open, fetch and close as it does automatically.


Hmmm....you should have just quoted Ross's comment above:

rleishman wrote on Sun, 29 October 2006 05:56
To use a cursor, you must first OPEN it. You may do this with the OPEN statement, or it will be done for you in a cursor FOR-LOOP.


Previous Topic: constraint type
Next Topic: How to get date in words like July 9, 2003 using to_date function
Goto Forum:
  


Current Time: Mon Dec 05 06:52:30 CST 2016

Total time taken to generate the page: 0.09365 seconds