Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> PL/SQL LOOP where statement not matching
I have a table of parts, dates, and quantity. Each part *should* be
in the table 13 times, with 13 different dates (weeks) and quantities.
However, while some parts do have the 13 weeks, some have just a few, not necessarily in any consecutive order.
What I have done is try to write a PL/SQL loop to load the 13 dates into a cursor, go through the table and pick out any rows that do *not* have an entry with that date, and insert the part, (missing) date, and quantity of '0'.
Below is the statement, but the jist of my problem is the sql times out because it cannot expand the tablespace - which, of course means that my statement is in an endless loop somewhere, which is the real issue. (There are a few additional commented out attempts to match the part and date of the current cursor(s) in the loop, but still no luck.)
I guess my question is why are the 'where' statements failing. I have also tried NOT IN and NOT EXISTS statements on the part/date match, but, again, no good.
(I don't even know if I need the part_list cursor.)
Thanks for any help!
DECLARE
CURSOR dates is
SELECT unique (t_date - to_char(t_date, 'D')+2)
FROM t_1
ORDER BY 1; -- [returns 13 dates]
CURSOR part_list is
SELECT unique part
FROM t_1; -- [currently 124 parts]
curr_date DATE;
curr_part varchar2(20);
BEGIN
DBMS_OUTPUT.enable(32000);
OPEN part_list;
LOOP
FETCH part_list INTO curr_part;
EXIT WHEN part_list%NOTFOUND;
![]() |
![]() |