Re: BULK BIND

From: Jeff Landers <jlanders_at_convergys.com>
Date: Fri, 06 Oct 2000 14:36:49 -0400
Message-Id: <10641.118708@fatcity.com>

Koivu, Lisa wrote:

>
>
> good morning all -
>
> Has anyone used this statement? Seems to me that BULK BIND could
> cause a PL/SQL program to become excessively large while executing if
> too many records are returned. Any insights are appreciated, I'm just
> curious.
>
> Thanks
>
> Lisa Rutland Koivu
> Oracle Database Administrator
> Qode.com
> 4850 North State Road 7
> Suite G104
> Fort Lauderdale, FL 33319
>
> V: 954.484.3191, x174
> F: 954.484.2933
> C: 954.658.5849
> http://www.qode.com
>
> "The information contained herein does not express the opinion or
> position of Qode.com and cannot be attributed to or made binding upon
> Qode.com."

  Hi Again Lisa
Thought maybe an example would save you some time. Here is a function utilizing the bulk collect
and bind. By using the collect with the limt clause, I can control the amount of data I pull
into memory. Yes, need to be careful.

The only problem I have had is capturing ORA errors that occur during the bulk update. Oracle
is doing a graceful exit without notifying me. Although not shown in the example below, I have
set up pragma's to detect FK violations. I force the error to occur and the still the code will
exit from the bulk update without hitting the exception clause. Will send it to you when I get
it figured out.

      function do_usage_unbld_mthly_index (   v_table_name varchar2,
                                                v_cycle_cd varchar2,
                                                v_cycle_end_dt varchar2,

                                                v_usage_partition

number) return integer is

                type rid_array is table of usage_unbld_mthly_index.rowid

                        index by binary_integer;

                my_rid          rid_array;
                rows            natural := 10000;
                err_num         number;
                err_msg         varchar2(100);

                CURSOR c1 is select rowid
                        from usage_unbld_mthly_index
                        where cycle_cd = v_cycle_cd and
                                v_cycle_end_dt = v_cycle_end_dt and
                                floor(mod(seg_seq_nbr,1000)/100) =

v_usage_partition;

                begin
                        open c1;
                        loop
                                fetch c1 bulk collect into my_rid limit
rows;
                                exit when c1%NOTFOUND;

                                forall indx in my_rid.FIRST..my_rid.LAST

                                        delete from
usage_unbld_mthly_index
                                        where rowid = my_rid(indx);
                                commit;

                                my_rid.DELETE;
                        end loop;

                        if my_rid.COUNT > 0 then
                                forall indx in my_rid.FIRST..my_rid.LAST

                                        delete from
usage_unbld_mthly_index
                                        where rowid = my_rid(indx);
                                commit;
                        end if;

                        close c1;
                        return 0;
                exception

                        when others then
                                err_num := SQLCODE;
                                err_msg := SUBSTR(SQLERRM, 1, 100);
                                dbms_output.put_line ('errNum: '||
err_num);
                                dbms_output.put_line ('errmsg: '||

Received on Fri Oct 06 2000 - 13:36:49 CDT