passing PL/SQL table as a parameter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: passing PL/SQL table as a parameter

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    passing PL/SQL table as a parameter

    declare
    pos_mods_rowid price_event_pkg.rowid_type;
    i number;
    j number;
    begin
    price_event_pkg.pos_items_stage(1, 100, sysdate, pos_mods_rowid);
    dbms_output.put_line(pos_mods_rowid(1));
    i:=price_event_pkg.delete_pos_mods(pos_mods_rowid);
    end;
    /

    I have a package that outputs pl/sql table of rowids and uses that rowids to delete rows from the table after processing.

    The problem is when I pass the collection (pl/sql table) to the procedure it deletes only the last row. What is wrong. Here is the code that deletes the row.


    FUNCTION DELETE_POS_MODS (pos_mods_rowid in rowid_type) Return Number
    Is
    v_commit_level Number := 5000;
    l_cnt1 Number := 0;
    indx Number := 0;
    Begin
    FOR indx IN pos_mods_rowid.FIRST .. pos_mods_rowid.LAST
    loop
    dbms_output.put_line(pos_mods_rowid(indx)));
    Delete
    From pos_mods
    Where rowid = pos_mods_rowid(indx);
    l_cnt1 := l_cnt1 + 1;
    If Mod(l_cnt1,v_commit_level) = 0 Then
    Commit;
    End If;
    End Loop;
    Commit;
    Return l_cnt1;
    Exception
    ........
    End DELETE_POS_MODS;
    /

  2. #2
    Join Date
    May 2004
    Location
    St. Louis, MO
    Posts
    4
    What is the output from the dbms_output.put_line lines of code in the unnamed block and in the function?

    In the unnamed block, this is line 6:
    price_event_pkg.pos_items_stage(1, 100, sysdate, pos_mods_rowid);
    pos_mods_rowid may be coming back from procedure price_event_pkg.pos_items_stage with only one value - the maximum rowid value.

    After the line above, add the following lines:
    dbms_output.put_line('unnamed block count' || pos_mods_rowid.COUNT);
    dbms_output.put_line('unnamed block first' || pos_mods_rowid.FIRST);
    dbms_output.put_line('unnamed block last' || pos_mods_rowid.LAST);
    What output does this give?


    As far as your function, for debugging, after line 7 (the FOR indx . . .line) add:
    dbms_output.put_line('function count' || pos_mods_rowid.COUNT);
    dbms_output.put_line('function first' || pos_mods_rowid.FIRST);
    dbms_output.put_line('function last' || pos_mods_rowid.LAST);
    What output does this give?

    In the function, this is line 9:
    dbms_output.put_line(pos_mods_rowid(indx)));
    There are two left (open) parenthesis and three right (close) parentheses. Should one of the right (close) parentheses be deleted?

  3. #3
    Join Date
    May 2004
    Posts
    4
    It looks like your collection of rowids contain only one item, and your load the successive rowids in the some item. Due to this, at the end of the operation the collection contains only the last rowid.

    I don't see anything wrong in the code listed in your message. If you do not find yourself the bug in the rest of the code, please provide:

    - the declaration of price_event_pkg.rowid_type

    - the procedure declaration and the code fragment from price_event_pkg.pos_items_stage which loads pos_mods_rowid

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