|
-
Dear all,
I wrote a store procedure, but i don't know how to solve the follow problem:
LINE/COL ERROR
-------- -----------------------------------------------------------------
41/8 PL/SQL: Statement ignored
41/31 PLS-00330: invalid use of type name or subtype name
The coding is:
CREATE OR REPLACE PROCEDURE Invoice(Gname in VARCHAR2 DEFAULT NULL) IS
URLproc CONSTANT VARCHAR2(60) :=
'http://moscow.cityu.edu.hk:12010/50222970/plsql/';
URLimg CONSTANT VARCHAR2(60) :=
'http://moscow.cityu.edu.hk:12001/csoweb01/images/';
CURSOR cInvoice(vGuestNo Number) IS
SELECT B.*, G.name, G.address, R.price,
TRUNC(SYSDATE-date_from) days
FROM Booking B, Guest G, Room R
where B.room_no=R.room_no AND B.occupant=G.guest_no
and date_from <= TRUNC(SYSDATE) AND date_to >=TRUNC(SYSDATE)
and payer IN
(SELECT guest_no from Guest where guest_no=vGuestNo)
order by R.room_no;
CURSOR cGuest IS
SELECT name, address FROM Guest WHERE guest_no IN
(SELECT DISTINCT payer FROM Booking
WHERE date_from<=TRUNC(SYSDATE) AND date_to>=TRUNC(SYSDATE))
ORDER BY name;
CURSOR cGetNo (vName CHAR, vAddr CHAR) IS
SELECT guest_no from Guest Where name=vName AND address=vAddr;
recIv cInvoice%ROWTYPE;
recGt cGuest%ROWTYPE;
recPn cGetNo%ROWTYPE;
vPayName VARCHAR2(22);
vPayAddr VARCHAR2(44);
BEGIN
IF Gname IS NOT NULL THEN
vPayName := SUBSTR(Gname, 1, INSTR(Gname, '|') - 2);
vPayAddr := SUBSTR(Gname, INSTR(Gname, '|')+2, LENGTH(Gname) - INSTR(Gname,'|'));
IF cInvoice%ROWCOUNT=1 THEN
htp.htmlOpen;
htp.headOpen;
htp.title('Invoice');
htp.headClose;
htp.bodyOpen;
htp.img(URLimg||'hotel_logo.jpg');
htp.para;
htp.header(1, 'Invoice');
END IF;
htp.print('Date: '||DATE||'');
htp.br;
htp.print('Payer: '||vPayName||' from '||vPayAddr||'');
htp.para;
htp.print('');
htp.print('| Room | ');
htp.print('Occupant | ');
htp.print('Address | ');
htp.print('Start date | ');
htp.print('No of days | ');
htp.print('Price/day | ');
htp.print('Total charge | ');
FOR recPn IN cGetNo(vPayName, vPayAddr) LOOP
FOR recIv IN cInvoice(recPn.guest_no) LOOP
htp.print('| '||recIv.room_no||' | ');
htp.print(''||recIv.occupant||' | ');
htp.print(''||recIv.address||' | ');
htp.print(''||recIv.date_from||' | ');
htp.print(''||recIv.days||' | ');
htp.print(''||recIv.price||' | ');
END LOOP;
END LOOP;
htp.anchor(URLproc||'Main_menu', 'Back to Main Menu');
htp.bodyClose;
htp.htmlClose;
ELSE
/***************************
* Display Get Invoice form *
***************************/
htp.htmlOpen;
htp.headOpen;
htp.title('Get Invoice');
htp.headClose;
htp.bodyOpen;
htp.img(URLimg||'hotel_logo.jpg');
htp.para;
htp.header(1, 'Get Invoice');
htp.print('Get the charge details up to today for all the rooms currently booked by the requested guest.');
htp.para;
OPEN cGuest;
LOOP
FETCH cGuest INTO recGt;
EXIT WHEN cGuest%NOTFOUND;
IF cGuest%ROWCOUNT=1 THEN
htp.formOpen(URLproc||'Invoice');
htp.formSelectOpen('Gname','Select the name of the guest requesting the invoice:
');
END IF;
htp.formSelectOption(recGt.name||'| '||recGt.address);
END LOOP;
/****************************
* Display No Guest message *
****************************/
IF cGuest%ROWCOUNT=0 THEN
htp.fontOpen('red');
htp.print('No guest is currently staying in our hotel!');
htp.fontClose;
htp.para;
ELSE
htp.formSelectClose;
htp.para;
htp.formSubmit;
htp.formReset;
htp.formClose;
END IF;
CLOSE cGuest;
htp.anchor(URLproc||'Main_menu', 'Back to Main Menu');
htp.bodyClose;
htp.htmlClose;
END IF;
END;
/
show errors
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
|
Reply With Quote