Oracle Illustrated

Jun 25 2010   6:42AM GMT

Migrating from 9i to 11g – Collections Enhancements



Posted by: Lakshmi Venkatesh
Tags:

1 Indicies of

New feature in Oracle 10g.

In FORALL collections.FIRST .. collections.LAST it is not possible to use the indices sequntially if the collection is sparse. But, the same can be handled in Oracle 10g by using FORALL IN INDICES OF keyword.

create table emp1(empno number, still_employed varchar2(20))

insert into emp1 values (1, ‘Y’);
insert into emp1 values (2, ‘N’);
insert into emp1 values (3, ‘Y’);

Oracle 9i
SET SERVEROUTPUT ON SIZE 10000
DECLARE
TYPE emp_id_list IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
TYPE emp_list IS TABLE OF emp1%ROWTYPE;
emp_id_tab emp_id_list ;
emp_tab emp_list := emp_list();
BEGIN

emp_Tab.extend;
emp_tab(1).empno := 10;
emp_Tab.extend;
emp_tab(2).empno := 100;
emp_Tab.extend;
emp_Tab.extend;
emp_tab(4).empno := 1000;

emp_Tab.extend;
emp_id_tab(1) := ‘Y’;
emp_Tab.extend;
emp_id_tab(2) := ‘N’;
emp_Tab.extend;
emp_Tab.extend;
emp_id_tab(4) := ‘Y’;

FORALL i IN emp_id_tab.first .. emp_id_tab.last
UPDATE EMP1 SET ROW = emp_tab(i)
WHERE still_employed = emp_id_tab(i);
END;
/

OUTPUT

DECLARE
*
ERROR at line 1:
ORA-22160: element at index [3] does not exist
ORA-06512: at line 24

Oracle 11g
SET SERVEROUTPUT ON SIZE 10000
DECLARE
TYPE emp_id_list IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
TYPE emp_list IS TABLE OF emp1%ROWTYPE;
emp_id_tab emp_id_list ;
emp_tab emp_list := emp_list();
BEGIN

emp_Tab.extend;
emp_tab(1).empno := 10;
emp_Tab.extend;
emp_tab(2).empno := 100;
emp_Tab.extend;
emp_Tab.extend;
emp_tab(4).empno := 1000;

emp_Tab.extend;
emp_id_tab(1) := ‘Y’;
emp_Tab.extend;
emp_id_tab(2) := ‘N’;
emp_Tab.extend;
emp_Tab.extend;
emp_id_tab(4) := ‘Y’;

FORALL i IN INDICES OF emp_id_tab
UPDATE EMP1 SET ROW = emp_tab(i)
WHERE still_employed = emp_id_tab(i);
END;
/

OUTPUT

PL/SQL procedure successfully completed.

2 Values of

New feature in Oracle 10g.

VALUES OF clause enables to match the elements of one collection against the value of another collection and helps to perform DML operations based on the same.

DELETE FROM EMP1;

Oracle 9i
SET SERVEROUTPUT ON SIZE 10000
DECLARE
TYPE emp_id_list IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
emp_id_tab emp_id_list;

TYPE emp_list IS TABLE OF emp1%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab emp_list;
BEGIN

emp_id_tab(1) := 10;
emp_id_tab(2) := 9;
emp_id_tab(3) := 8;

SELECT rownum,’Y’ BULK COLLECT INTO emp_tab
FROM emp
WHERE ROWNUM <= 50;

FORALL i IN emp_id_tab.FIRST .. emp_id_tab.LAST
INSERT INTO EMP1 VALUES emp_tab(i);
END;
/

OUTPUT

PL/SQL procedure successfully completed.

EMPNO STILL_EMPLOYED
1 Y
2 Y
3 Y

Only solution is – create nested table and perform and compare empno against TABLE(nested_table) in SELECT statement.

Oracle 11g
SET SERVEROUTPUT ON SIZE 10000
DECLARE
TYPE emp_id_list IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
emp_id_tab emp_id_list;

TYPE emp_list IS TABLE OF emp1%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab emp_list;
BEGIN

emp_id_tab(1) := 10;
emp_id_tab(2) := 9;
emp_id_tab(3) := 8;

SELECT rownum,’Y’ BULK COLLECT INTO emp_tab
FROM emp
WHERE ROWNUM <= 50;

FORALL i IN VALUES OF emp_id_tab
INSERT INTO EMP1 VALUES emp_tab(i);
END;
/

OUTPUT

PL/SQL procedure successfully completed.

EMPNO STILL_EMPLOYED
10 Y
9 Y
8 Y

If we note the above results, the VALUES OF clause exactly matches the elements of one collection vs the elements of other collection and inserts values. This cannot be achieved in Oracle 9i unless we explicitly match up the elements in the WHERE clause.

3 Error handling

Oracle 9i
SET SERVEROUTPUT ON SIZE 10000
DECLARE
TYPE emp_id_list IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
emp_id_tab emp_id_list;

TYPE emp_list IS TABLE OF emp1%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab emp_list;
BEGIN

emp_id_tab(1) := 10;
emp_id_tab(2) := 9;
emp_id_tab(3) := 100;

SELECT rownum,’Y’ BULK COLLECT INTO emp_tab
FROM emp
WHERE ROWNUM <= 50;

FORALL i IN emp_id_tab.FIRST .. emp_id_tab.LAST
INSERT INTO EMP1 VALUES emp_tab(i);

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error message ‘ || SQLERRM);
END;
/

OUTPUT

PL/SQL procedure successfully completed.
EMPNO STILL_EMPLOYED
1 Y
2 Y
3 Y
This will still work – because it does not try to match the elements.

Oracle 11g
SET SERVEROUTPUT ON SIZE 10000
DECLARE
TYPE emp_id_list IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
emp_id_tab emp_id_list;

TYPE emp_list IS TABLE OF emp1%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab emp_list;
BEGIN

emp_id_tab(1) := 10;
emp_id_tab(2) := 9;
emp_id_tab(3) := 100;

SELECT rownum,’Y’ BULK COLLECT INTO emp_tab
FROM emp
WHERE ROWNUM <= 50;

FORALL i IN VALUES OF emp_id_tab
INSERT INTO EMP1 VALUES emp_tab(i);

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error message ‘ || SQLERRM);

END;
/

OUTPUT

Error message ORA-22160: element at index [100] does not exist

4 Collect
New feature from Oracle 11g Release 1

COLLECT enables to transform rows into columns just based on a single function.

Running it on sql developer -

Oracle 11g
select deptno , collect(ename) enm from scott.emp
group by deptno ;

10 VARCHAR(CLARK,KING,MILLER)
20 VARCHAR(SMITH,FORD,ADAMS,SCOTT,JONES)
30 VARCHAR(ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD)

We cannot do something like this !!

SELECT qry.dno, SUBSTR(qry.enm,8) empnm from
(select deptno dno , collect(ename) enm from scott.emp
group by deptno ) qry

Running it on sqlplus with report related command-

SQL> break on deptno skip 1;

SQL> select deptno , collect(ename) as empnm from scott.emp group by deptno ;

Oracle 11g
DEPTNO EMPNM
——————————————————————————–
10 SYSTPi7LdWP3QSeisIuh7s78iIg==(‘CLARK’, ‘KING’, ‘MILLER’)
20 SYSTPi7LdWP3QSeisIuh7s78iIg==(‘SMITH’, ‘FORD’, ‘ADAMS’, ‘SCOTT’, ‘JONES’)
30 SYSTPi7LdWP3QSeisIuh7s78iIg==(‘ALLEN’, ‘BLAKE’, ‘MARTIN’, ‘TURNER’, ‘JAMES’, ‘WARD’)

5 Collection Assignment

Now, the collections assignment is improved with various additional features viz., multiset union, multiset intersect, multiset distcint etc.,
SET SERVEROUTPUT ON
DECLARE
TYPE software_tab IS TABLE OF VARCHAR2(1000);
soft_list_1 software_tab := software_tab(‘Oracle’,'C’,'C#’,'VB’,'Sql’);
soft_list_2 software_tab := software_tab(‘Oracle’,'C’,'PHP’,'Java’);
soft_list_3 software_tab;

BEGIN
– this is as usual
soft_list_3 := soft_list_1;
FOR i IN soft_list_3.first .. soft_list_3.last LOOP
DBMS_OUTPUT.put_line(‘Assignment eg ‘ || soft_list_3(i));
END LOOP;

soft_list_3 := soft_list_1 MULTISET UNION DISTINCT soft_list_2;
FOR i IN soft_list_3.first .. soft_list_3.last LOOP
DBMS_OUTPUT.put_line(‘Multiset union distinct eg ‘ || soft_list_3(i));
END LOOP;

soft_list_3 := soft_list_1 MULTISET INTERSECT DISTINCT soft_list_2;
FOR i IN soft_list_3.first .. soft_list_3.last LOOP
DBMS_OUTPUT.put_line(‘Multiset intersect distinct eg ‘ || soft_list_3(i));
END LOOP;

soft_list_3 := soft_list_1 MULTISET EXCEPT soft_list_2;
FOR i IN soft_list_3.first .. soft_list_3.last LOOP
DBMS_OUTPUT.put_line(‘Multiset except eg ‘ || soft_list_3(i));
END LOOP;

soft_list_3 := soft_list_1 MULTISET INTERSECT soft_list_2;
FOR i IN soft_list_3.first .. soft_list_3.last LOOP
DBMS_OUTPUT.put_line(‘Multiset intersect eg ‘ || soft_list_3(i));
END LOOP;

soft_list_3 := soft_list_1 MULTISET EXCEPT DISTINCT soft_list_2;
FOR i IN soft_list_3.first .. soft_list_3.last LOOP
DBMS_OUTPUT.put_line(‘Multiset except distinct eg ‘ || soft_list_3(i));
END LOOP;

soft_list_3 := soft_list_1 MULTISET UNION soft_list_2;
FOR i IN soft_list_3.first .. soft_list_3.last LOOP
DBMS_OUTPUT.put_line(‘Multiset union eg ‘ || soft_list_3(i));
END LOOP;
END;
/
OUTPUT-
Assignment eg Oracle
Assignment eg C
Assignment eg C#
Assignment eg VB
Assignment eg Sql
Multiset union distinct eg Oracle
Multiset union distinct eg C
Multiset union distinct eg C#
Multiset union distinct eg VB
Multiset union distinct eg Sql
Multiset union distinct eg PHP
Multiset union distinct eg Java
Multiset intersect distinct eg Oracle
Multiset intersect distinct eg C
Multiset except eg C#
Multiset except eg VB
Multiset except eg Sql
Multiset intersect eg Oracle
Multiset intersect eg C
Multiset except distinct eg C#
Multiset except distinct eg VB
Multiset except distinct eg Sql
Multiset union eg Oracle
Multiset union eg C
Multiset union eg C#
Multiset union eg VB
Multiset union eg Sql
Multiset union eg Oracle
Multiset union eg C
Multiset union eg PHP
Multiset union eg Java
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07

6 Improved comparisons

SET SERVEROUTPUT ON
DECLARE
TYPE software_tab IS TABLE OF VARCHAR2(10);
software_list_1 software_tab := software_tab(‘Oracle’,'C’,'C#’,'VB’,'Sql’);
software_list_2 software_tab := software_tab(‘Oracle’,'C’,'PHP’,'Java’);
software_list_3 software_tab;
BEGIN
IF (software_list_3 IS NULL) AND (software_list_1 IS NOT NULL) THEN
DBMS_OUTPUT.put_line(‘Value – list3 is null and list1 is not null’);
END IF;
software_list_3 := software_list_1;
IF (software_list_3 = software_list_1) AND (software_list_3 != software_list_2) THEN
DBMS_OUTPUT.put_line(‘list3 = list1 and list3 != list2 ‘);
END IF;

IF (SET(software_list_2) SUBMULTISET software_list_1) AND (software_list_1 NOT SUBMULTISET software_list_2) THEN
DBMS_OUTPUT.put_line( ‘list2 submultiset of list1 and list1 is not sub multiset of list2′);
END IF;

DBMS_OUTPUT.put_line(‘Duplicates related print list 2 -’ || CARDINALITY(software_list_2));

DBMS_OUTPUT.put_line( ‘Remove duplicates list2 – ‘ || CARDINALITY(SET(software_list_2)) || ‘ – Remove duplicates’);

IF software_list_2 IS NOT A SET THEN
DBMS_OUTPUT.put_line( ‘software_list_2 has duplicates’);
END IF;

IF software_list_3 IS NOT EMPTY THEN
DBMS_OUTPUT.put_line( ‘List3 is not empty’);
END IF;
END;
/
OUTPUT-
Value – list3 is null and list1 is not null
list3 = list1 and list3 != list2
Duplicates related print list 2 -4
Remove duplicates list2 – 4 – Remove duplicates
List3 is not empty
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

7 Improved SET operator
Normal assignment assigns all the values including duplicates – SET assignment removes the duplicates before assignment.

SET SERVEROUTPUT ON
DECLARE
TYPE software_tab IS TABLE OF VARCHAR2(10);
software_list_1 software_tab := software_tab(‘Oracle’,'C’,'C#’,'VB’,'Sql’, ‘Oracle’,'Sql’);
software_list_2 software_tab;

BEGIN
software_list_2 := software_list_1;
FOR i IN software_list_2.first .. software_list_2.last LOOP
DBMS_OUTPUT.put_line(‘normal Assignment – ‘ || software_list_2(i));
END LOOP;

software_list_2 := SET(software_list_1);
FOR i IN software_list_2.first .. software_list_2.last LOOP
DBMS_OUTPUT.put_line(‘set assignment – ‘ || software_list_2(i));
END LOOP;

END;
/
OUTPUT-
normal Assignment – Oracle
normal Assignment – C
normal Assignment – C#
normal Assignment – VB
normal Assignment – Sql
normal Assignment – Oracle
normal Assignment – Sql
set assignment – Oracle
set assignment – C
set assignment – C#
set assignment – VB
set assignment – Sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04

References : From internet.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: