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;
Hi!
Error is here:
htp.print('Date: '||DATE||'');
'DATE' is reserved word. You probably meant SYSDATE.
By the way, such pages are very easy to create using PL/SQL Server Pages technology (PSP). We recommend you to take a look at our Dynamic PSP offering. Dynamic PSP is well suited for Oracle8i-based web applications of virtually any complexity. It provides a web-based development interface (applications are developed from a web browser), advanced features for effective code reuse, debugging and profiling. You can get a free trial copy at http://www.dpsp-yes.com.
Posting Permissions
Reply With Quote
Bookmarks