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:
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: