Store procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Store procedure

  1. #1
    Join Date
    Feb 2001
    Posts
    15

    Question

    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('');
    htp.print('');
    htp.print('');
    htp.print('');
    htp.print('');
    htp.print('');
    htp.print('');

    FOR recPn IN cGetNo(vPayName, vPayAddr) LOOP
    FOR recIv IN cInvoice(recPn.guest_no) LOOP
    htp.print('');
    htp.print('');
    htp.print('');
    htp.print('');
    htp.print('');
    htp.print('');
    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

  2. #2
    Join Date
    Sep 2001
    Posts
    15
    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.

    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]
    Victor
    www.dynamicpsp.com

  3. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  
    RoomOccupantAddressStart dateNo of daysPrice/dayTotal charge
    '||recIv.room_no||''||recIv.occupant||''||recIv.address||''||recIv.date_from||''||recIv.days||''||recIv.price||'



    Click Here to Expand Forum to Full Width