1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cursor attributes

Discussion in 'SQL PL/SQL' started by Hans Poppe, Mar 31, 2019.

  1. Hans Poppe

    Hans Poppe Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Belgiƫ
    Hi,
    We need to catch the fact that no rows are returning from a cursor. Therefore I wanted to use the attribute
    %notfound attribute. It seems that this is not working with a for loop. It doesn't seem to enter the for loop in that case. Are those correct assumptions an should we use in that case Open-fetch-close cursor so the cursor
    gets fetched?
    Thanks
    Hans
     
  2. Hans Poppe

    Hans Poppe Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Belgiƫ
    Solved the issue this way :

    For rec_book in cur_book
    loop
    v_count := cur_book%rowcount;

    if rec_book.peract='N' then
    logger.log('Period '||rec_book.bperd||' - '||rec_book.bjaar||' is not active', l_scope, null, l_params);
    pck_cc_error.add_error(
    i_refer => 'PERIOD',
    i_mesce => 'ACTIVE',
    i_mtype => 'MESSAGE',
    i_dfmsg => 'Period is not active'
    );
    exit;
    elsif rec_book.perdm is null then
    logger.log(rec_book.bperd||' <--> '||rec_book.bjaar||' ok', l_scope, null, l_params);
    if rec_book.dgbact='N' then
    logger.log('Period'|| rec_book.bperd||' - '||rec_book.bjaar||' is not active', l_scope, null, l_params);
    pck_cc_error.add_error(
    i_refer => 'PERIOD',
    i_mesce => 'ACTIVE',
    i_mtype => 'MESSAGE',
    i_dfmsg => 'Period is not active'
    );
    exit;
    elsif rec_book.dgbdm is null then
    o_bkyear := rec_book.bjaar;
    o_bkper := rec_book.bperd;
    logger.log('Bookyear --> '||rec_book.bjaar, l_scope, null, l_params);
    logger.log('Accounting period -->'||rec_book.bperd, l_scope, null, l_params);
    exit;
    else
    logger.log('Journal for this period '||rec_book.bperd||' - '||rec_book.bjaar||' is not open', l_scope, null, l_params);
    end if;
    end if;
    end loop;
    If v_count is null then
    logger.log('No periods found by cursor',l_scope, null, l_params);
    pck_cc_error.add_error(
    i_refer => 'PERIOD',
    i_mesce => 'EXIST',
    i_mtype => 'MESSAGE',
    i_dfmsg => 'Periods could not be retrieved'
    );
    else
    logger.log('Number of rows picked up by cursor: '||v_count, l_scope, null, l_params);
    end if;
    logger.log_end(null, l_scope, null, l_params);
    exception
    when others then
    logger.log_exception(null, l_scope, null, l_params);
    raise;
    end determine_bookyear_and_period;
    end;