Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Cursor when creating HTML file

Status
Not open for further replies.
kafmil

kafmil

Technical User
Jul 15, 2002
71
AU
I am trying to create a HTML file from a stored proc but i just cannot seem to get it to work. Is it possible to use a cursor in the same stored proc as creating a file? When i try to run it in isqlplus it always asks me to input a value for id. I am completly stumped any help would be greatly appreciated. The code is down below:

CREATE OR REPLACE PROCEDURE ShowSchedule
(iVenueID INTEGER, dtDate DATE)
IS
fileID UTL_FILE.FILE_TYPE;
dir VARCHAR2(100);
BEGIN

dir := 'C:/Inetpub/-- open the file for writing
fileID := UTL_FILE.FOPEN(dir, 'ShowSchedule.inc', 'w');
-- write what you have read

UTL_FILE.PUT_LINE(fileID, '<HTML>');
UTL_FILE.PUT_LINE(fileID, '<TITLE>SCHEDULE FOR ' + dtDate + '</TITLE>');
UTL_FILE.PUT_LINE(fileID, '<BODY>');
UTL_FILE.PUT_LINE(fileID, '<TABLE BORDER="0">');
UTL_FILE.PUT_LINE(fileID, '<TR>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Event');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Facility');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Category');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Date');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Time');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Venue');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Location');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '<TH>');
UTL_FILE.PUT_LINE(fileID, 'Capacity');
UTL_FILE.PUT_LINE(fileID, '</TH>');
UTL_FILE.PUT_LINE(fileID, '</TR>');

DECLARE CURSOR curEvents IS
SELECT
EVENT.EVENTID,
EVENT.NAME as EVENTNAME,
FACILITIES.FACILITIESID,
FACILITIES.DESCRIPTION AS FACILITYDESC,
CATEGORY.CATEGORYID,
CATEGORY.DESCRIPTION AS CATEGORYDESC,
SCHEDULE.SCHEDULEID,
SCHEDULE.STARTDATE,
SCHEDULE.STARTTIME,
VENUE.VENUEID,
VENUE.NAME,
VENUE.LOCATION,
VENUE.SEATCAPACITY
FROM
EVENT
INNER JOIN
FACILITIES
ON
EVENT.FACILITIESID = FACILITIES.FACILITIESID
INNER JOIN
CATEGORY
ON
CATEGORY.CATEGORYID = EVENT.CATEGORYID
INNER JOIN
SCHEDULE
ON
SCHEDULE.EVENTID = EVENT.EVENTID
INNER JOIN
VENUE
ON
VENUE.VENUEID = SCHEDULE.VENUEID
WHERE
VENUE.VENUEID = iVenueID
AND
(SCHEDULE.STARTDATE = dtDate OR dtDate IS NULL)
ORDER BY
SCHEDULE.STARTDATE DESC, SCHEDULE.STARTTIME;

intEVENTID INTEGER;
varEVENTNAME VARCHAR2(100);
intFACILITIESID INTEGER;
varFACILITYDESC VARCHAR2(100);
intCATEGORYID INTEGER;
varCATEGORYDESC VARCHAR2(250);
intSCHEDULEID INTEGER;
dtSTARTDATE DATE;
timSTARTTIME TIMESTAMP;
intVENUEID INTEGER;
varVENUENAME VARCHAR2(100);
intLOCATION VARCHAR2(100);
intSEATCAPACITY NUMBER (4,4);



OPEN curEvents;

FETCH
curEvents
INTO
intEVENTID,
varEVENTNAME,
intFACILITIESID,
varFACILITYDESC,
intCATEGORYID,
varCATEGORYDESC,
intSCHEDULEID,
dtSTARTDATE,
timSTARTTIME,
intVENUEID,
varVENUENAME,
intLOCATION,
intSEATCAPACITY;

WHILE curEvents%FOUND LOOP

UTL_FILE.PUT_LINE(fileID, '<TR>');
UTL_FILE.PUT_LINE(fileID, '<A HREF="INDEX.HTML?PAGE=EVENT&ID="' + curEvents.intEVENTID + '>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.varEVENTNAME);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.varFACILITYDESC);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.varCATEGORYDESC);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.dtSTARTDATE);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.timSTARTTIME);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.varVENUENAME);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.intLOCATION);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '<TD>');
UTL_FILE.PUT_LINE(fileID, curEvents.intSEATCAPACITY);
UTL_FILE.PUT_LINE(fileID, '</TD>');
UTL_FILE.PUT_LINE(fileID, '</A>');
UTL_FILE.PUT_LINE(fileID, '</TR>');

FETCH
curEvents
INTO
intEVENTID,
varEVENTNAME,
intFACILITIESID,
varFACILITYDESC,
intCATEGORYID,
varCATEGORYDESC,
intSCHEDULEID,
dtSTARTDATE,
timSTARTTIME,
intVENUEID,
varVENUENAME,
intLOCATION,
intSEATCAPACITY;
END LOOP;


UTL_FILE.PUT_LINE(fileID, '</TABLE>');
UTL_FILE.PUT_LINE(fileID, '</BODY>');
UTL_FILE.PUT_LINE(fileID, '</HTML>');
-- close the file
UTL_FILE.FCLOSE(fileID);
-- commit and unlock the record
COMMIT;
END ShowSchedule;
 
Sort by date Sort by votes
If you launch this from sql*plus then it interprets your &ID as lexical variable and asks for its value. You may redefine variable marker with SET DEFINE OFF|<OTHER CHARACTER>

Regards, Dima
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Server Guy
  • Locked
  • Question
Solaris 10 update 9 iso file
Replies
0
Views
381
Server Guy
Server Guy
Olutee360
  • Locked
  • Question
Group by using values in a range 1
Replies
4
Views
418
Olutee360
Olutee360
Livia8
  • Locked
  • Question
Can deleted files/ directories (rm -r) be retrieved again?
Replies
6
Views
698
Livia8
Livia8
EM1107
  • Locked
  • Question
Insert date and time using UTC time in a DATE column
Replies
14
Views
2K
EM1107
EM1107
DeShaun
  • Locked
  • Question
Having issue with ORA 00920. sql works when run seperately
Replies
3
Views
336
Beilstwh
Beilstwh

Part and Inventory Search

Sponsor

Back
Top