Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Loop Variable Index Value is Invalid

442975Apr 25 2006 — edited Apr 25 2006
Hi. This will be a basic question for most but since I'm just starting to teach myself it's got me stuck. Below is a procedure that I wrote that queries a view (table) and returns info in the form of an email. I can get it to work if I only have one declaration (eventdate- DTG) but when I add "host" I get the above error. Essentially all I need to do is run the query in the "FOR" line and get the multi-column results.
Any help would be appreciated...

DECLARE
l_eventdate varchar2(32767);
l_host varchar2(32767);
BEGIN
FOR c1 in (SELECT DTG, HOST FROM MV$HH24_FW15SVCS WHERE to_date(dtg, 'MM-DD-YYYY HH24') > (sysdate-1)
order by to_date(dtg, 'MM-DD-YYYY HH24') DESC)
LOOP
l_eventdate:= l_eventdate || c1.dtg ||' ';
l_host:= l_host || c1.host ||' ';
END LOOP;
HTMLDB_MAIL.SEND(P_TO => 'me@me.com',
P_FROM => 'Automated Reporting Tool',
P_BODY => '

Note: This message was automatically generated.

This the the activity summary events for the past day:

'|| l_eventdate || l_host,
P_SUBJ => 'Summary');
END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 23 2006
Added on Apr 25 2006
9 comments
1,511 views