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
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.
Small example of PSP:
<table border=1>
<tr>
<th>Room</th>
<th>Occupant</th>
<th>Address</th>
<th>Start date</th>
<th>No of days</th>
<th>Price/day</th>
</tr>
<% FOR recIv IN cInvoice(recPn.guest_no) LOOP %>
<tr>
<td> <%= recIv.room_no %> </td>
<td> <%= recIv.occupant %> </td>
<td> <%= recIv.address %> </td>
<td> <%= recIv.date_from %> </td>
<td> <%= recIv.days %> </td>
<td> <%= recIv.price %> </td>
</tr>
<% end loop; %>
</table>
[Edited by victorp on 11-03-2001 at 09:13 AM]
