pankaj_gauba

 Profile Answers by pankaj_gauba Questions by pankaj_gauba

• Jun 30th, 2006

 

dd

swathi

• Jul 13th, 2006

 

type sw_table is table of numberindex by binary_integer;sw_tbl sw_table;max_size number:=50;cursor sw_cur is select no from pltable_test;beginopen sw_cur;loopfetch sw_cur bulk collect into sw_tbl limit max_size; dbms_output.put_line('sw_tbl count = '||sw_tbl.count);if sw_tbl.count = 0 thenexit;end if;for i in sw_tbl.first..sw_tbl.last loopdbms_output.put_line('sw_tbl = '||sw_tbl(i));end loop;forall i in sw_tbl.first..sw_tbl.lastupdate pltable_testset no = no+sw_tbl(i)where no = sw_tbl(i);end loop;end;

maria antony

• Aug 12th, 2006

 

During bullk collcect you can save the exception ( if any raised during bullk collect ) and then process the exeption one by one.

See the below given example:

DECLARE    TYPE NumList IS TABLE OF NUMBER;   num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);   errors  NUMBER;BEGIN   FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS      DELETE * FROM emp WHERE sal > 500000/num_tab(i);EXCEPTION WHEN OTHERS THEN    -- this is not in the doco, thanks to JL for pointing this out   errors := SQL%BULK_EXCEPTIONS.COUNT;   dbms_output.put_line('Number of errors is ' || errors);   FOR i IN 1..errors LOOP         -- Iteration is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;         -- Error code is SQL%BULK_EXCEPTIONS(i).ERROR_CODE;   END LOOP;END;

mariashylaja

 Profile 

• Aug 12th, 2006

 

DECLARE    TYPE NumList IS TABLE OF NUMBER;   num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);   errors  NUMBER;BEGIN   FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS      DELETE * FROM emp WHERE sal > 500000/num_tab(i);EXCEPTION WHEN OTHERS THEN    -- this is not in the doco, thanks to JL for pointing this out   errors := SQL%BULK_EXCEPTIONS.COUNT;   dbms_output.put_line('Number of errors is ' || errors);   FOR i IN 1..errors LOOP         -- Iteration is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;         -- Error code is SQL%BULK_EXCEPTIONS(i).ERROR_CODE;   END LOOP;END;

mariashylaja

 Profile 

• Aug 12th, 2006

 

During bulk collect you can save the exception and then you can process the exception.

Look at the below given example:

DECLARE    TYPE NumList IS TABLE OF NUMBER;   num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);   errors  NUMBER;BEGIN   FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS      DELETE * FROM emp WHERE sal > 500000/num_tab(i);EXCEPTION WHEN OTHERS THEN    -- this is not in the doco, thanks to JL for pointing this out   errors := SQL%BULK_EXCEPTIONS.COUNT;   dbms_output.put_line('Number of errors is ' || errors);   FOR i IN 1..errors LOOP         -- Iteration is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;         -- Error code is SQL%BULK_EXCEPTIONS(i).ERROR_CODE;   END LOOP;END;