PLSQL LIMIT

declare
 cursor a_cur is
  select program_id
 from airplanes;
 type myarray is table of a_cur%rowtype;
 cur_array myarray;
begin
  open a_cur;
  loop
    fetch a_cur bulk collect into cur_array limit 1000;
    exit when a_cur%notfound;
  end loop;
  close a_cur;
end;

/
FOR More info Please go through the URL:  http://psoug.org/reference/array_processing.html

PL/SQL EXTEND

PL/SQL EXTEND

Definition:

EXTEND is one of the Oracle PL/SQL collection methods which is used with nested tables and VARRAYS to append single or multiple elements to the collection. Note that EXTEND cannot be used with associative arrays. EXTEND has three forms:

    EXTEND, which adds a single NULL instance.
    EXTEND(n) which adds multiple NULL instances. The number of instances is specified by n.
    EXTEND(n,m) which appends n copies of instance m to the collection.

Note that forms one and two cannot be used for NOT NULL specified collections.

Example Usage:

The PL/SQL block below declares a PL/SQL table collection and appends the first cell at the end of the collection.

DECLARE

       type psoug_tab is table of number;
       ptab psoug_tab;

BEGIN

        ptab := psoug_tab();
        ptab.extend;
        ptab(1) := 100;
        dbms_output.put_line('value at index(1) is '||ptab(1));
        ptab.extend(5,1);
        dbms_output.put_line('value at index(4) is '||ptab(4));

END;



value at index(1) is 100

value at index(4) is 100



PL/SQL procedure successfully completed.

PL/SQL Normal Insert and Bulk Insert

PL/SQL NORMAL INSERT AND BULK INSERT

create table forall_test (
  id           number(10),
  code         varchar2(10),
  description  varchar2(50));

alter table forall_test add (
  constraint forall_test_pk primary key (id));

alter table forall_test add (
  constraint forall_test_uk unique (code));

set serveroutput on


declare
  type t_forall_test_tab is table of forall_test%rowtype;

  l_tab    t_forall_test_tab := t_forall_test_tab();
  l_start  number;
  l_size   number            := 10000;
begin
  -- populate collection.
  for i in 1 .. l_size loop
    l_tab.extend;

    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := to_char(i);
    l_tab(l_tab.last).description := 'description: ' || to_char(i);
  end loop;

--  execute immediate 'truncate table forall_test';

  -- time regular inserts.
  l_start := dbms_utility.get_time;

  for i in l_tab.first .. l_tab.last loop
    insert into forall_test (id, code, description)
    values (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  end loop;
 

  dbms_output.put_line('normal inserts: ' ||
                       (dbms_utility.get_time - l_start));
 
  execute immediate 'truncate table forall_test';

  -- time bulk inserts.
  l_start := dbms_utility.get_time;

  forall i in l_tab.first .. l_tab.last
    insert into forall_test values l_tab(i);

  dbms_output.put_line('bulk inserts  : ' ||
                       (dbms_utility.get_time - l_start));

  commit;
end;

/