passing PL/SQL table as a parameter

[newbie5]

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;
/

[moretolearn]

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?

[agoodman]

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