Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Oracle Collection in PLSQL

Leave a Comment / COLLECTIONS / By

Oracle Collection in PLSQL

Examples for Oracle Collection In PLSQL : Associative Arrays example:

Associative Arrays In Blocks:

--Examples for Oracle Collection In PLSQL : Associative Arrays In Blocks:

set serveroutput on;
declare
  type t_empid is table of employees.employee_id%type index by pls_integer;
  v_empid t_empid;
type t_sal is table of employees.salary%type index by pls_integer;
v_sal t_sal;
v_error_msg varchar2(32767) := null;
v_error_count pls_integer := 0;
v_msg varchar2(30000);
begin
begin
  select employee_id, salary
  bulk collect into v_empid, v_sal
  from employees
where department_id = 20
  order by employee_id;
exception
when others then
  v_error_msg := dbms_utility.format_error_backtrace;
  dbms_output.put_line('Error ->'||v_error_msg);
end;
begin
for i in 1..v_empid.count
loop
  dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary ->'||v_sal(i));
end loop;
exception
when others then
  v_error_msg := dbms_utility.format_error_backtrace;
  dbms_output.put_line('Error ->'||v_error_msg);
end;
end;
/

Output:

Employeeid -> 201 Salary -> 13000
Employeeid -> 202 Salary -> 6000

PL/SQL procedure successfully completed.

Return out of plsql block, if Collection is empty:

declare
  type t_empid is table of employees.employee_id%type index by pls_integer;
  v_empid t_empid;
  type t_sal is table of employees.salary%type index by pls_integer;
  v_sal t_sal;
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
begin
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 301
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  if v_empid.count = 0 then
    dbms_output.put_line('Collection is empty..Please fill the collection..');
    return;
  end if;
  begin
    for i in 1..v_empid.count
    loop
      dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
end;
/

Output:

Collection is empty..Please fill the collection..

Insert data through Associative Arrays:

--Examples for Oracle Collection In PLSQL : Insert data through Associative Arrays:

create table emp_test_sal
as
select employee_id, salary
from employees
where 1 = 2;

set serveroutput on;
declare
  type t_empid is table of employees.employee_id%type index by pls_integer;
  v_empid t_empid;
  type t_sal is table of employees.salary%type index by pls_integer;
  v_sal t_sal;
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
begin
  execute immediate 'truncate table emp_test_sal';
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 20
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  if v_empid.count = 0 then
    dbms_output.put_line('Collection is empty..Please fill the collection..');
    return;
  end if;
  begin
    for i in 1..v_empid.count
    loop
      insert into emp_test_sal values(v_empid(i), v_sal(i));
      dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
end;
/

Output:

Output of an Example of Oracle Collection in PLSQL
Output of an Example of Oracle Collection in PLSQL

Associative Arrays with Bulk Collect & Forall:

--Examples for Oracle Collection In PLSQL : Associative Arrays with Bulk Collect & Forall:

set serveroutput on;
declare
  type t_empid is table of employees.employee_id%type index by pls_integer;
  v_empid t_empid;
  type t_sal is table of employees.salary%type index by pls_integer;
  v_sal t_sal;
  v_error_msg varchar2(32767) := null;
  v_bulk_exception exception;
  pragma exception_init(v_bulk_exception, -24381);
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
begin
  execute immediate 'truncate table emp_test_sal';
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 30
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;

  begin
    forall i in 1..v_empid.count save exceptions
    insert into emp_test_sal values(v_empid(i), v_sal(i));
  exception
    when v_bulk_exception then
      v_error_count := sql%bulk_exceptions.count;
      for i in 1..v_error_count
      loop
        dbms_output.put_line('Error '||i||' Array Index: '||sql%bulk_exceptions(i).error_index||' Message : '||sqlerrm(-         sql%bulk_exceptions(i).error_code));
      end loop;
  end;
end;
/

Output:

select * from emp_test_sal;

EMPLOYEE_ID                 SALARY
114                         11000
115                         3100
116                         2900
117                         2800
118                         2600
119                         2500

Examples for Oracle Collection In PLSQL : Nested Table Examples:

Nested Table In Plsql block:

 --Examples for Oracle Collection In PLSQL : Nested Table In Plsql block:

set serveroutput on;
declare
  type t_empid is table of employees.employee_id%type;
  v_empid t_empid := t_empid();
  type t_sal is table of employees.salary%type;
  v_sal t_sal := t_sal();
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
begin
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 30
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  begin
    for i in 1..v_empid.count
    loop
      dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
end;
/

Output:

Employeeid -> 114 Salary -> 11000
Employeeid -> 115 Salary -> 3100
Employeeid -> 116 Salary -> 2900
Employeeid -> 117 Salary -> 2800
Employeeid -> 118 Salary -> 2600
Employeeid -> 119 Salary -> 2500

PL/SQL procedure successfully completed.

Return out of plsql block, if Collection is empty:

declare
  type t_empid is table of employees.employee_id%type;
  v_empid t_empid := t_empid();
  type t_sal is table of employees.salary%type;
  v_sal t_sal := t_sal();
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
begin
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 501
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  if v_empid.count = 0 then
    dbms_output.put_line('Collection is empty..Please fill the collection..');
    return;
  end if;
  begin
    for i in 1..v_empid.count
    loop
      dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
end;
/

Output:

Collection is empty..Please fill the collection..

Insert data through Nested Tables:

--Examples for Oracle Collection In PLSQL : Insert data through Nested Tables:

create table emp_test_sal
as
select employee_id, salary
from employees
where 1 = 2;

set serveroutput on;
declare
  type t_empid is table of employees.employee_id%type;
  v_empid t_empid;
  type t_sal is table of employees.salary%type;
  v_sal t_sal;
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
begin
  execute immediate 'truncate table emp_test_sal';
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 30
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  if v_empid.count = 0 then
    dbms_output.put_line('Collection is empty..Please fill the collection..');
    return;
  end if;
  begin
    for i in 1..v_empid.count
    loop
      insert into emp_test_sal values(v_empid(i), v_sal(i));
      dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
end;
/

Output:

Employeeid -> 114 Salary -> 11000
Employeeid -> 115 Salary -> 3100
Employeeid -> 116 Salary -> 2900
Employeeid -> 117 Salary -> 2800
Employeeid -> 118 Salary -> 2600
Employeeid -> 119 Salary -> 2500

select * from emp_test_sal;

EMPLOYEE_ID               SALARY
114                       11000
115                       3100
116                       2900
117                       2800
118                       2600
119                       2500

Nested Tables with Bulk Collect & Forall:

--Examples for Oracle Collection In PLSQL : Nested Tables with Bulk Collect & Forall:

declare
  type t_empid is table of employees.employee_id%type;
  v_empid t_empid := t_empid();
  type t_sal is table of employees.salary%type;
  v_sal t_sal := t_sal();
  v_error_msg varchar2(32767) := null;
  v_bulk_exception exception;
  pragma exception_init(v_bulk_exception, -24381);
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
begin
  execute immediate 'truncate table emp_test_sal';
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 50
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;

  begin
    forall i in 1..v_empid.count save exceptions
    insert into emp_test_sal values(v_empid(i), v_sal(i));
  exception
    when v_bulk_exception then
      v_error_count := sql%bulk_exceptions.count;
      for i in 1..v_error_count
      loop
        dbms_output.put_line('Error '||i||' Array Index: '||sql%bulk_exceptions(i).error_index||' Message : '||sqlerrm(-sql%bulk_exceptions(i).error_code));
      end loop;
  end;
end;
/
select * from emp_test_sal;

EMPLOYEE_ID              SALARY
114                      11000
115                       3100
116                       2900
117                       2800
118                       2600
119                       2500

Nested Tables with Extend method:

--Examples for Oracle Collection In PLSQL : Nested Tables with Extend method:

set serveroutput on;
declare
  type t_emptab is table of emp_test_sal%rowtype;
  v_emptab t_emptab := t_emptab();
  v_error_msg varchar2(32767) := null;
  v_bulk_exception exception;
  pragma exception_init(v_bulk_exception, -24381);
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
  --
  cursor c1
  is
  select employee_id, salary
  from employees
  where department_id = 50
  order by employee_id
  fetch first 6 rows only; 
  --
  begin
    execute immediate 'truncate table emp_test_sal';
    begin
      for i in c1
      loop
        v_emptab.extend;
        v_emptab(v_emptab.last) := i;
        dbms_output.put_line('Employeeid - >'||i.employee_id||' Salary -> '||i.salary);
      end loop;
    exception
      when others then
        v_error_msg := dbms_utility.format_error_backtrace;
        dbms_output.put_line('Error ->'||v_error_msg);
    end;
    --
    begin
      forall i in 1..v_emptab.count save exceptions
        insert into emp_test_sal values(v_emptab(i).employee_id, v_emptab(i).salary);
    exception
      when v_bulk_exception then
        v_error_count := sql%bulk_exceptions.count;
        for i in 1..v_error_count
        loop
          dbms_output.put_line('Error '||i||' Array Index: '||sql%bulk_exceptions(i).error_index||' Message : '||sqlerrm(-sql%bulk_exceptions(i).error_code));
        end loop;
    end;
end;
/
PL/SQL procedure successfully completed.

Employeeid - >120 Salary -> 8000
Employeeid - >121 Salary -> 8200
Employeeid - >122 Salary -> 7900
Employeeid - >123 Salary -> 6500
Employeeid - >124 Salary -> 5800
Employeeid - >125 Salary -> 3200

Varrays Examples:

Varrays In PLSQL Blocks:

--Examples for Oracle Collection In PLSQL : Varrays In PLSQL Blocks

declare
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
  v_array_count pls_integer := 0;
  type t_empid is varray(45) of employees.employee_id%type;
  v_empid t_empid := t_empid();
  type t_sal is varray(45) of employees.salary%type;
  v_sal t_sal := t_sal();
begin
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 50
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  begin
    for i in 1..v_empid.count
    loop
      dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
end;
/
Output:

Employeeid -> 114 Salary -> 11000
Employeeid -> 115 Salary -> 3100
Employeeid -> 116 Salary -> 2900
Employeeid -> 117 Salary -> 2800
Employeeid -> 118 Salary -> 2600
Employeeid -> 119 Salary -> 2500
declare
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
  v_array_count pls_integer := 0;
  procedure get_emp_data(p_deptno in number)
  is
    type t_empid is varray(45) of employees.employee_id%type;
    v_empid t_empid := t_empid();
    type t_sal is varray(45) of employees.salary%type;
    v_sal t_sal := t_sal();
  begin
    begin
      select employee_id, salary
      bulk collect into v_empid, v_sal
      from employees
      where department_id = p_deptno
      order by employee_id;
    exception
      when others then
        v_error_msg := dbms_utility.format_error_backtrace;
        dbms_output.put_line('Error ->'||v_error_msg);
    end;
    begin
      for i in 1..v_empid.count
      loop
        dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
      end loop;
    exception
      when others then
        v_error_msg := dbms_utility.format_error_backtrace;
        dbms_output.put_line('Error ->'||v_error_msg);
    end;
end;
begin
  begin
    select count(1)
    into v_array_count
    from employees
    where department_id = 50;
  exception 
    when others then
    v_array_count := 0;
  end;
  if v_array_count > 0 then
    get_emp_data(50);
  end if;
end;
/

Return out of plsql block if collection is empty:

declare
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
  v_array_count pls_integer := 0;
  procedure get_emp_data(p_deptno in number)
  is
    type t_empid is varray(45) of employees.employee_id%type;
    v_empid t_empid := t_empid();
    type t_sal is varray(45) of employees.salary%type;
    v_sal t_sal := t_sal();
  begin
    begin
      select employee_id, salary
      bulk collect into v_empid, v_sal
      from employees
      where department_id = p_deptno
      order by employee_id;
    exception
      when others then
        v_error_msg := dbms_utility.format_error_backtrace;
        dbms_output.put_line('Error ->'||v_error_msg);
    end;
    begin
      for i in 1..v_empid.count
      loop
        dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
      end loop;
    exception
      when others then
        v_error_msg := dbms_utility.format_error_backtrace;
        dbms_output.put_line('Error ->'||v_error_msg);
    end;
  end;
begin
  begin
    select count(1)
    into v_array_count
    from employees
    where department_id = 501;
  exception 
    when others then
      v_array_count := 0;
  end;
  if v_array_count > 0 then
    get_emp_data(50);
  else
    dbms_output.put_line('Collection is empty..please fill the collection..');
  end if;
end;
/

Inserting data through Varrays:

--Examples for Oracle Collection In PLSQL : Inserting data through Varrays:

declare
  v_error_msg varchar2(32767) := null;
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
  v_array_count pls_integer := 0;
  type t_empid is varray(6) of employees.employee_id%type;
  v_empid t_empid := t_empid();
  type t_sal is varray(6) of employees.salary%type;
  v_sal t_sal := t_sal();
begin
  execute immediate 'truncate table emp_test_sal';
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 30
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  begin
    for i in 1..v_empid.count
    loop
      insert into emp_test_sal values(v_empid(i), v_sal(i));
      dbms_output.put_line('Employeeid -> '||v_empid(i)||' Salary -> '||v_sal(i));
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
end;
/
PL/SQL procedure successfully completed.

Employeeid -> 114 Salary -> 11000
Employeeid -> 115 Salary -> 3100
Employeeid -> 116 Salary -> 2900
Employeeid -> 117 Salary -> 2800
Employeeid -> 118 Salary -> 2600
Employeeid -> 119 Salary -> 2500

Varrays with Bulk Collect & Forall:

--Examples for Oracle Collection In PLSQL : Varrays with Bulk Collect & Forall:

set serveroutput on;
declare
  type t_empid is varray(45) of employees.employee_id%type;
  v_empid t_empid := t_empid();
  type t_sal is varray(45) of employees.salary%type;
  v_sal t_sal := t_sal();
  v_error_msg varchar2(32767) := null;
  v_bulk_exception exception;
  pragma exception_init(v_bulk_exception, -24381);
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
begin
  execute immediate 'truncate table emp_test_sal';
  begin
    select employee_id, salary
    bulk collect into v_empid, v_sal
    from employees
    where department_id = 50
    order by employee_id;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  --
  begin
    forall i in 1..v_empid.count save exceptions
    insert into emp_test_sal values(v_empid(i), v_sal(i));
  exception
    when v_bulk_exception then
      v_error_count := sql%bulk_exceptions.count;
      for i in 1..v_error_count
      loop
        dbms_output.put_line('Error '||i||' Array Index: '||sql%bulk_exceptions(i).error_index||' Message : '||sqlerrm(-sql%bulk_exceptions(i).error_code));
      end loop;
  end;
end;
/
select count(1) count from emp_test_sal;

COUNT
45


Varray with Extend method:

--Examples for Oracle Collection In PLSQL : Varray with Extend method:

set serveroutput on;
declare
  type t_emptab is varray(6) of emp_test_sal%rowtype;
  v_emptab t_emptab := t_emptab();
  v_error_msg varchar2(32767) := null;
  v_bulk_exception exception;
  pragma exception_init(v_bulk_exception, -24381);
  v_error_count pls_integer := 0;
  v_msg varchar2(30000);
  --
  cursor c1
  is
  select employee_id, salary
  from employees
  where department_id = 50
  order by employee_id
  fetch first 6 rows only; 
  --
begin
  execute immediate 'truncate table emp_test_sal';
  begin
    for i in c1
    loop
      v_emptab.extend;
      v_emptab(v_emptab.last) := i;
      dbms_output.put_line('Employeeid - >'||i.employee_id||' Salary -> '||i.salary);
    end loop;
  exception
    when others then
      v_error_msg := dbms_utility.format_error_backtrace;
      dbms_output.put_line('Error ->'||v_error_msg);
  end;
  --
  begin
    forall i in 1..v_emptab.count save exceptions
      insert into emp_test_sal values(v_emptab(i).employee_id, v_emptab(i).salary);
  exception
    when v_bulk_exception then
      v_error_count := sql%bulk_exceptions.count;
      for i in 1..v_error_count
      loop
        dbms_output.put_line('Error '||i||' Array Index: '||sql%bulk_exceptions(i).error_index||' Message : '||sqlerrm(-sql%bulk_exceptions(i).error_code));
      end loop;
  end;
end;
/
PL/SQL procedure successfully completed.

Employeeid - >120 Salary -> 8000
Employeeid - >121 Salary -> 8200
Employeeid - >122 Salary -> 7900
Employeeid - >123 Salary -> 6500
Employeeid - >124 Salary -> 5800
Employeeid - >125 Salary -> 3200

RELATED TOPICS:

Author

Leave a Comment

Your email address will not be published.