pl/sql loop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: pl/sql loop

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Jul 2001
    Posts
    181
    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;
    /

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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
    Last edited by Sameer; 02-06-2003 at 10:28 AM.

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width