pl/sql loop

Hi,

I have the following

BEGIN
<>
FOR r_idh IN (SELECT instrumentId, lastSalePrice from tibex_instrumentIndexMap) LOOP
v_lastTradePrice:=NULL;
v_currentDate:=trunc(to_date('20030120','YYYYMMDD'));
v_currentDateString:=to_char(v_currentDate,'YYYYMMDD');
-- v_currentDate:=trunc(sysdate)
<>
WHILE v_lastTradePrice IS NULL LOOP
v_startOfDay:=eod_epoch.date_to_epoch(v_currentDateString,'000000');
v_endOfDay:=eod_epoch.date_to_epoch(v_currentDateString,'235959');
<>
FOR r_ex IN c_latestBookTrade(r_idh.instrumentId) LOOP
v_lastTradePrice:=r_ex.price;
EXIT Latest_BookTrade;
END LOOP Latest_BookTrade;
IF v_lastTradePrice IS NULL THEN
v_currentDate:=v_currentDate -1;
EXIT daily_loop WHEN v_currentDate < k_startDate;
END IF;
END LOOP daily_loop;

IF (v_lastTradeprice IS NULL) THEN
dbms_output.put_line ('Untraded Instrument ' || r_idh.instrumentId);
ELSIF
v_lastTradeprice != r_idh.lastSalePrice THEN
dbms_output.put_line ('HAwk Alert ' || r_idh.instrumentId);
END IF;
END LOOP Instrument_Loop;
END;
/


The v_currentDate variable should be set to trunc(sysdate) and when it goes into the loop it should go backwards day by day until it finds a dbms_output.put_line ('HAwk Alert ' || r_idh.instrumentId);

It seems to pick out the correct info when you hard code the date into
v_currentDate, but it doesn't seem to be working in the loop which goes back one day at a time. when v_currentdate is set to sydate which is ideally what I want. when it is set to sysdate it only justifies the dbms_output.put_line ('Untraded Instrument ' || r_idh.instrumentId) which I know is wrong!

Hope this is clear and any shedding of light would be much appreciated..


Thanks in advance

Don't seem to have k_startDate defined. I also note that:
v_currentDateString:=to_char(v_currentDate,'YYYYMMDD');
-- v_currentDate:=trunc(sysdate)
would be the wrong way round if the -- are removed.
What is lurking in the <> bits?

This is the whole proc
some variables are not being used but please do not be concerned with them also the hashed out section is what was there previously.

CREATE OR REPLACE PROCEDURE marlon as

-- ENV Parameters: - These need editing:
-- k_utlDir CONSTANT t_big_string:='/opt/oracle/admin/SRVTEST/utl';
-- k_logFile CONSTANT t_big_string:='lastsaleprice.sql';
-- ETS Parameters
k_today CONSTANT DATE:=trunc(sysdate);
k_startDate CONSTANT DATE:=trunc(to_date('20030201','YYYYMMDD'));
-- k_endDate CONSTANT DATE:=trunc(to_date('20030204','YYYYMMDD'));
k_hybridBoard CONSTANT tibex_board.boardId%TYPE:='ES';
-- General processing Variables:
FH UTL_FILE.FILE_TYPE;
v_currentDate DATE;
v_currentDateString VARCHAR2(8);
v_currentYear tibex_instrumentDailyHist.tradeYear%TYPE;
v_currentMonth tibex_instrumentDailyHist.tradeMonth%TYPE;
v_currentDay tibex_instrumentDailyHist.tradeDay%TYPE;
v_startOfDay tibex_execution.timestamp%TYPE;
v_endOfDay tibex_execution.timestamp%TYPE;
v_lastTradePrice tibex_execution.price%TYPE;
v_newClosePrice tibex_instrumentDailyHist.close%TYPE;
CURSOR c_latestBookTrade(
p_instrumentId tibex_instrumentindexmap.instrumentId%TYPE) IS
SELECT price
FROM tibex_execution
WHERE boardId=k_hybridBoard
AND instrumentId=p_instrumentId
AND timestamp between v_startOfDay and v_endOfDay
AND executionId not in (
SELECT executionId
FROM tibex_execution
WHERE boardId=k_hybridBoard
AND instrumentId=p_instrumentId
AND timestamp between v_startOfDay and v_endOfDay
AND status=1
)
ORDER BY timestamp desc;
BEGIN
<>
FOR r_idh IN (SELECT instrumentId, lastSalePrice from tibex_instrumentIndexMap) LOOP
v_lastTradePrice:=NULL;
v_currentDate:=trunc(to_date('20030120','YYYYMMDD'));
v_currentDateString:=to_char(v_currentDate,'YYYYMMDD');
-- v_currentDate:=trunc(sysdate)
<>
WHILE v_lastTradePrice IS NULL LOOP
v_startOfDay:=eod_epoch.date_to_epoch(v_currentDateString,'000000');
v_endOfDay:=eod_epoch.date_to_epoch(v_currentDateString,'235959');
<>
FOR r_ex IN c_latestBookTrade(r_idh.instrumentId) LOOP
v_lastTradePrice:=r_ex.price;
EXIT Latest_BookTrade;
END LOOP Latest_BookTrade;
IF v_lastTradePrice IS NULL THEN
v_currentDate:=v_currentDate -1;
EXIT daily_loop WHEN v_currentDate < k_startDate;
END IF;
END LOOP daily_loop;
IF (v_lastTradeprice IS NULL) THEN
dbms_output.put_line ('Untraded Instrument ' || r_idh.instrumentId);
ELSIF
v_lastTradeprice != r_idh.lastSalePrice THEN
dbms_output.put_line ('HAwk Alert ' || r_idh.instrumentId);
END IF;
END LOOP Instrument_Loop;
END;
/

Quote:

---

Originally posted by netbar
This is the whole proc
some variables are not being used but please do not be concerned with them also the hashed out section is what was there previously.

---

Please make use of tags

Code:

---

Put your code here

---

it makes easy to read

Hi netbar!
Sameer is right - help us to help you. If you stripped out the unused code, indented the IF's and LOOP's, used a code tag, etc, it would be much easier for someone to spot the problem - including you!