Home » SQL & PL/SQL » SQL & PL/SQL » Odd behavior with bulk collect (Oracle PL/SQL database 9i on Unix operating system)
Odd behavior with bulk collect [message #278831] Mon, 05 November 2007 17:18 Go to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

A strange behavior occurs when the result of the division of the number of records that are bulk collected by the number at which the bulk collect is set, is an even number.

If this result is exact (no remainder), even though the updates are performing correctly, Oracle gives ORA-06502(Numeric or value conversion error).
Example
I have 2000 records that we select from a table and I bulk collect into the cursor in 500 records at a time.
If the result of 2000/500 is an exact number Oracle shoots the error above. It appears that Oracle is trying another loop after finishing up the 4 times of updates. The problem gets fixed if the bulk collect is set to 501.
Anybody came across this behaviour?
In the meantime we found a fix for this with a pragma init exception which is raised at the end and handled acordingly.

Re: Odd behavior with bulk collect [message #278850 is a reply to message #278831] Tue, 06 November 2007 00:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I would suggest for check for appropriate EXIT Condition in appropriate place . I dont have any other suggestioin as no code is available.

Thumbs Up
Rajuvan.
Re: Odd behavior with bulk collect [message #278856 is a reply to message #278831] Tue, 06 November 2007 00:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I agree with Rajuvan. This is probably due to a flaw in your code.
Please post your code here so we can help you check it.
Re: Odd behavior with bulk collect [message #279018 is a reply to message #278856] Tue, 06 November 2007 11:20 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Thanks so much.
Here is my code.

PROCEDURE RESET_CHANGE_PARAM(
   process_date   IN   DATE DEFAULT TRUNC (SYSDATE)
)
IS
    CURSOR  chg_live_cur is
   SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
          mt.transit_time, mt.effective_date
 	 FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl,
	   					t_ep_shinr ts
				 WHERE  mt.action_code = 'CHG-LIVE'
  				  AND  mt.catalog = tc.catal
  				  AND  tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				  AND  mt.ship_to_number = ts.shinr
  				  AND  vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id;

	CURSOR chg_rst_cur IS
				SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
          			   mt.transit_time, mt.effective_date
 	 			  FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl,
	   					t_ep_shinr ts
				 WHERE (mt.action_code = 'CHANGEPLUS'   OR
                       mt.action_code = 'CHANGE_MINUS' OR
                       mt.action_code = 'CHG-RST'      OR
                       mt.action_code = 'CHANGE')
				   AND mt.catalog = tc.catal
  				   AND tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				   AND mt.ship_to_number = ts.shinr
  				   AND vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id;

	-- collection of scalars
	TYPE itemloc_typ IS TABLE OF sales_data.item_id%TYPE INDEX BY PLS_INTEGER;
    TYPE stf_typ IS TABLE OF sales_data.store_max%TYPE INDEX BY PLS_INTEGER;
    TYPE fixfill_typ IS TABLE OF sales_data.fixture_fill%TYPE INDEX BY PLS_INTEGER;
    TYPE action_typ IS TABLE OF dp.t_src_matrix.action_code%TYPE INDEX BY PLS_INTEGER;
    TYPE date_typ IS TABLE OF dp.t_src_matrix.effective_date%TYPE INDEX BY PLS_INTEGER;

action action_typ;
item itemloc_typ;
loc  itemloc_typ;


stmin stf_typ;
stmax stf_typ;
facing stf_typ;
fixfill fixfill_typ;

itemN itemloc_typ;
locN itemloc_typ;
stminN stf_typ;
stmaxN stf_typ;
facingN stf_typ;
fixfillN fixfill_typ;


--Date and time variables
trTime stf_typ;
efDate date_typ;
nb_err  Pls_integer ;
total  Pls_integer := 0 ;



v_start_date  DATE := SYSDATE;
v_end_date   DATE ;
cnt_rec    NUMBER := 0;
cnt_rec_ch NUMBER := 0;
v_time  NUMBER := 0;
v_seq   NUMBER := 0;


    -- create an exception handler for ORA-24381
    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN
 --Check the content of products_data

	BEGIN
		SELECT COUNT(1) INTO cnt_rec FROM dp.t_src_matrix
		WHERE action_code = 'CHG-LIVE';
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

 --Check the content of sales_cur
	BEGIN
		SELECT COUNT(1) INTO cnt_rec_ch FROM dp.t_src_matrix
		WHERE action_code = 'CHANGEPLUS'   OR
              action_code = 'CHANGE_MINUS' OR
              action_code = 'CHG-RST'      OR
              action_code = 'CHANGE';
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

 IF cnt_rec > 0 THEN
	OPEN chg_live_cur;
 LOOP
     FETCH chg_live_cur BULK COLLECT INTO
	 item, loc, stmin, stmax, facing, fixfill, trTime, efDate LIMIT 500; (--here I replaced with 501 and it worked)

	 BEGIN
	 FORALL i IN item.FIRST .. item.LAST SAVE EXCEPTIONS
            UPDATE sales_data
               SET store_max = stmax(i),
                   store_min = stmin(i),
                     facings = facing(i),
                fixture_fill = fixfill(i)
             WHERE item_id = item(i)
               AND location_id = loc(i)
               AND store_max IS NOT NULL
               AND store_min IS NOT NULL
               AND facings IS NOT NULL;
     COMMIT;
     EXCEPTION
     WHEN dml_errors THEN NULL;
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
    END;
EXIT WHEN chg_live_cur%NOTFOUND;
END LOOP;
CLOSE chg_live_cur;
ELSE NULL;
END IF;


--For Chg-Rst
 IF cnt_rec_ch > 0 THEN
	OPEN chg_rst_cur;
 LOOP
     FETCH chg_rst_cur BULK COLLECT INTO
	 item, loc, stmin, stmax, facing, fixfill, trTime, efDate LIMIT 1000;
     --Update with values from the cursor if sales_date < ef_date - trtime
	 BEGIN
	 FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS

	  UPDATE dr.sales_data
       SET store_max = NVL(store_max, stmax(i)),
           store_min = NVL(store_min, stmin(i)),
             facings = NVL (facings, facing(i)),
        fixture_fill = NVL (fixture_fill, fixfill(i))
     WHERE   item_id = item(i) AND
         location_id = loc(i) AND
	       sales_date < TO_DATE (efDate(i), 'DD-MON-YYYY') - trTime(i) AND
          (item_id, location_id) IN
          (SELECT s.item_id, s.location_id
             FROM mdp_matrix m, sales_data s
            WHERE s.item_id = item(i)
              AND s.location_id = loc(i)
              AND s.sales_date = TRUNC(PROCESS_DATE)
              AND s.item_id = m.item_id
              AND s.location_id = m.location_id
              AND (NVL (s.store_max, m.store_max) <> stmax(i)
               OR  NVL (s.store_min, m.store_min) <> stmin(i)
               OR  NVL (s.facings, m.facings) <> facing(i)
               OR  NVL (s.fixture_fill, m.fixture_fill) <> fixfill(i))
			);
	COMMIT;
    EXCEPTION
         WHEN dml_errors THEN NULL;
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
    END;

	BEGIN
	 FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS
       --Set to NULL if sales_date > ef_date - transit time

               UPDATE dr.sales_data
                  SET store_max = NULL,
                      store_min = NULL,
                      facings = NULL,
                      fixture_fill = NULL
                WHERE item_id = item(i)
                  AND location_id = loc(i)
                  AND sales_date >=
                           TO_DATE (efDate(i), 'DD-MON-YYYY')
                         - (trTime(i));
     COMMIT;
     EXCEPTION
     WHEN dml_errors THEN NULL;
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
     END;
EXIT WHEN chg_rst_cur%NOTFOUND;
END LOOP;
CLOSE chg_rst_cur;
ELSE NULL;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
Re: Odd behavior with bulk collect [message #279070 is a reply to message #279018] Tue, 06 November 2007 16:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you always do a forall update, there will also be a forall update if the bulk collect retrieved no rows.
Move the exit up, to just after the fetch
Re: Odd behavior with bulk collect [message #279086 is a reply to message #279070] Tue, 06 November 2007 20:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Place the following immediately after your FETCH:
EXIT WHEN item.COUNT = 0;


%NOTFOUND returns true when a fetch returns fewer rows than the LIMIT. Placing %NOTFOUND immediately after the FETCH will cause the last partial array of rows to go unprocessed.

Ross Leishman
Re: Odd behavior with bulk collect [message #279150 is a reply to message #279086] Wed, 07 November 2007 02:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
oops.
Thanks for the correction, Ross.
Re: Odd behavior with bulk collect [message #279285 is a reply to message #279086] Wed, 07 November 2007 10:45 Go to previous message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Ross you are God. Thanks so much. Embarassed
Previous Topic: user_sequences table
Next Topic: Combining multiple rows into one row
Goto Forum:
  


Current Time: Sun Dec 04 14:44:59 CST 2016

Total time taken to generate the page: 0.22001 seconds