1 of 113

SAGE Computing Services

Customised Oracle Training Workshops and Consulting

Bulk Binds

Be A Bulk Binding Baron

​

Scott Wesley

Systems Consultant

& Trainer

​

​

​

​

​

2 of 113

3 of 113

who_am_i;

4 of 113

http://strategy2c.wordpress.com/2009/01/10/strategy-for-goldfish-funny-illustration-by-frits/

5 of 113

6 of 113

7 of 113

8 of 113

bulk binds

9 of 113

been around since 8i

10 of 113

not used often enough

11 of 113

binding is the assignment of values to

placeholders in SQL statements

my_column = :my_value

12 of 113

Bulk Binding is binding an array of values

my_column = my_array(i)

13 of 113

in-bind

my_variable

insert

update

merge

14 of 113

out-bind

my_variable

returning

15 of 113

define

my_variable

fetch

select

16 of 113

multiset

17 of 113

A quick word on Collections

18 of 113

lists or sets of information

19 of 113

invaluable

20 of 113

21 of 113

can be faster than SQL

22 of 113

cache frequent access in PGA

23 of 113

24 of 113

my_module (pt_array IN OUT NOCOPY t_array)

25 of 113

types of collections

26 of 113

  • Associative Arrays (aka index-by tables)
    • Only PL/SQL
    • Sparse
    • Can have negative indexes
    • Similar to hash table concept
    • Different datatypes for indexes
    • TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size_limit)];

​

TYPE emp_tab IS TABLE OF employees%ROWTYPE � INDEX BY PLS_INTEGER;

​

27 of 113

  • Nested Tables
    • Can use in SQL
    • Unbounded
    • Good for set operations
    • TYPE type_name IS TABLE OF element_type [NOT NULL];

​

TYPE top_emp IS TABLE OF employees.employee_id%TYPE;

​

CREATE TYPE galaxy AS TABLE OF star_object;

​

28 of 113

  • VARRAY
    • Specify maximums
    • Dense
    • Can use in SQL
    • Most similar to 3GL array
    • TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];

​

TYPE Calendar IS VARRAY(366) OF DATE;

​

CREATE TYPE rainbow AS VARRAY(7) OF VARCHAR2(64);

CREATE TYPE solar_system AS VARRAY(8) OF planet_object;

​

29 of 113

back to the business of binds

30 of 113

from the 8.1.7 documentation

31 of 113

32 of 113

Conventional Bind

Performance penalty for many context switches

Oracle Server

PL/SQL Runtime Engine

PL/SQL block

Procedural statement executor

SQL Engine

SQL statement executor

FOR r_emp IN c_emp LOOP

UPDATE emp

SET sal = sal * 1.1

WHERE empno = r_emp.empno;

END LOOP;

33 of 113

Bulk Bind

Much less overhead

Oracle Server

PL/SQL Runtime Engine

PL/SQL block

Procedural statement executor

SQL Engine

SQL statement executor

FORALL i IN INDICES OF t_emp

UPDATE emp

SET sal = sal * 1.1

WHERE empno = t_emp(i);

​

34 of 113

when would you use it?

35 of 113

plsql_optimize_level = 42

36 of 113

lifesgoldenrule.com

37 of 113

no bulk bind – do it in SQL

38 of 113

“If the DML statement affects four or more database rows, the use of bulk SQL can improve performance considerably”� - Oracle Documentation

39 of 113

SELECT everyone

BULK COLLECT

INTO my_array

FROM the_worlds_idiots;

40 of 113

SELECT everyone

BULK COLLECT

INTO my_array LIMIT 100

FROM the_worlds_idiots;

41 of 113

reoccurring SQL within PL/SQL loop

42 of 113

DML – LOOP FORALL

43 of 113

multiple operations on dataset – BULK COLLECT

44 of 113

http://www.sharenator.com/Before_After/stinejensen_530-65109.html

45 of 113

11g compound triggers – auditing

46 of 113

finally some coding examples?

47 of 113

DML Bind Example

-- Slower method, running the UPDATE

-- statements within a regular loop

FOR i IN t_emp.FIRST..t_emp.LAST LOOP

UPDATE emp_largish

SET sal = sal * 1.1

WHERE empno = t_emp(i);

END LOOP;

​

1.94 seconds

​

-- Efficient method, using a bulk bind on a VARRAY

FORALL i IN t_emp.FIRST..t_emp.LAST

UPDATE emp_largish

SET sal = sal * 1.1

WHERE empno = t_emp(i);

​

1.40 seconds

48 of 113

Collect Example

-- Slower method, assigning each

-- collection element within a loop.

v_index := 0;

FOR r_emp IN c_emp LOOP

v_index := v_index + 1;

t_emp(v_index).empno := r_emp.empno;

t_emp(v_index).ename := r_emp.ename;

END LOOP;

​

0.19 seconds

​

-- Efficient method, using a bulk bind

OPEN c_emp;

FETCH c_emp BULK COLLECT INTO t_emp;

CLOSE c_emp;

​

0.09 seconds

​

49 of 113

Example of Combination

-- Slower method, running update

-- statement within regular loop,

-- returning individual elements

FOR i IN t_emp.FIRST..t_emp.LAST LOOP

UPDATE emp_largish

SET sal = sal * 1.1

WHERE empno = t_emp(i)

RETURNING sal

INTO t_sal(i);

END LOOP;

​

0.75 seconds

​

-- Efficient method, using a bulk bind

-- and bulk collect

FORALL i IN t_emp.FIRST..t_emp.LAST

UPDATE emp_largish

SET sal = sal * 1.1

WHERE empno = t_emp(i)

RETURNING sal BULK COLLECT

INTO t_sal;

​

0.29 seconds

50 of 113

when wouldn’t you use it

51 of 113

lifesgoldenrule.com

52 of 113

SQL

can you just do it in SQL?

when you only need one iteration

just filter records with

where/intersect/minus

when you haven't tested it

with & without

>= 10g may silently do it for you

too much data?

53 of 113

what if I have a sparse collection?

54 of 113

Uh oh…

DECLARE

TYPE emp_tab IS TABLE OF emp.empno%TYPE INDEX BY PLS_INTEGER;

t_emp emp_tab;

CURSOR c_emp IS

SELECT empno FROM emp;

BEGIN

OPEN c_emp;

FETCH c_emp BULK COLLECT INTO t_emp;

CLOSE c_emp;

-- ...do a bunch of processing, including

-- something like:

t_emp.DELETE(3);

FORALL i IN t_emp.FIRST..t_emp.LAST

UPDATE emp

SET sal = sal * 1.1

WHERE empno = t_emp(i);

END;

/

​

ORA-22160: element at index [3] does not exist

ORA-06512: at line 15

55 of 113

processing spare collections

56 of 113

introduced in 10g

57 of 113

INDICES OF

allows iteration of all index values,

not just from lower to upper bound (tab.FIRST..tab.LAST)

58 of 113

VALUES OF

Create PL/SQL table effectively

indexing your collection

- process specific elements

- process in particular order

- process an element more than once

59 of 113

simple

FORALL i IN t_emp.FIRST..t_emp.LAST

UPDATE emp

SET sal = sal * 1.1

WHERE empno = t_emp(i);

​

FIRST

​

​

​

​

​

​

​

​

LAST

Updated all rows

In order of array listing

60 of 113

simple

FORALL i IN t_emp.FIRST..t_emp.LAST

UPDATE emp

SET sal = sal * 1.1

WHERE empno = t_emp(i);

​

FIRST

​

​

​

​

​

​

​

​

LAST

ORA-22160: element at index [3] does not exist

61 of 113

clever

FORALL i IN INDICES OF t_emp

UPDATE emp

SET comm = comm * 1.5

WHERE empno = t_emp(i);

​

​

​

7890

​

7988

7752

​

7521

7900

​

Updated rows within array

In order of array listing

t_emp(3) := 7890

t_emp(5) := 7988

t_emp(6) := 7752

t_emp(8) := 7521

t_emp(9) := 7900

62 of 113

advanced

​

​

7890

​

7988

7752

​

​

​

​

t_index(-2):= 6

t_index( 0):= 3

t_index( 3):= 5

t_index(10):= 6

t_emp(9) := 7900

t_emp(8) := 7521

t_emp(6) := 7752

t_emp(5) := 7988

t_emp(3) := 7890

FORALL i IN VALUES OF t_index

UPDATE emp

SET comm = comm * 1.5

WHERE empno = t_emp(i);

​

DECLARE

TYPE emp_tab IS TABLE OF emp.empno%TYPE INDEX BY PLS_INTEGER;

TYPE numlist IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;

t_emp emp_tab;

t_indexes numlist;

BEGIN

t_emp(3) := 7890;

t_emp(5) := 7988;

t_emp(6) := 7752;

t_emp(8) := 7521;

t_emp(9) := 7900;

​

t_indexes(-2) := 6;

t_indexes(0) := 3;

t_indexes(3) := 5;

t_indexes(10) := 6;

​

63 of 113

what if I have 9i?

64 of 113

CREATE OR REPLACE PACKAGE BODY sw_bulk_insert IS

PROCEDURE sw_insert (sw_tab IN t_sw_tab) IS

BEGIN

$IF dbms_db_version.ver_le_9 $THEN

DECLARE

l_dense t_sw_tab;

ln_index PLS_INTEGER := sw_tab.FIRST;

BEGIN

<< dense_loop >>

WHILE (l_index IS NOT NULL) LOOP

l_dense(l_dense.COUNT + 1) := sw_tab(l_index);

l_index := sw_tab.NEXT(l_index);

END LOOP dense_loop;

​

FORALL i IN 1..l_dense.COUNT

INSERT INTO sw_table VALUES l_dense(i);

END;

$ELSE

FORALL i IN INDICES OF sw_tab

INSERT INTO sw_table

VALUES sw_tab(i);

$END

END sw_insert;

END sw_bulk_insert;

65 of 113

​

DECLARE

t_emp t_emp_obj;

BEGIN

SELECT emp_obj(empno, ename, job, mgr, hiredate, sal, comm, deptno)

BULK COLLECT INTO t_emp

FROM emp;

​

-- Remove those with commission to create sparse collection

FOR i IN 1..t_emp.COUNT LOOP

IF t_emp(i).comm IS NOT NULL THEN

t_emp.DELETE(i);

END IF;

END LOOP;

​

-- No need for FORALL

INSERT INTO emp2

SELECT * FROM TABLE(CAST(t_emp AS t_emp_obj));

END;

/

​

CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=0;

CREATE OR REPLACE TYPE emp_obj AS OBJECT

(empno NUMBER(4)

,ename VARCHAR2(10)

,job VARCHAR2(9)

,mgr NUMBER(4)

,hiredate DATE

,sal NUMBER(7,2)

,comm NUMBER(7,2)

,deptno NUMBER(2))

/

​

CREATE OR REPLACE TYPE t_emp_obj AS TABLE OF emp_obj

/

​

66 of 113

can I do this "bunch of processing" in bulk, outside the db?

67 of 113

DECLARE

TYPE emp_tab IS TABLE OF emp.empno%TYPE INDEX BY PLS_INTEGER;

t_emp emp_tab;

CURSOR c_emp IS

SELECT empno FROM emp;

BEGIN

OPEN c_emp;

FETCH c_emp BULK COLLECT INTO t_emp;

CLOSE c_emp;

-- ...do a bunch of processing, including

-- something like:

t_emp.DELETE(3);

FORALL i IN t_emp.FIRST..t_emp.LAST

UPDATE emp

SET sal = sal * 1.1

WHERE empno = t_emp(i);

END;

/

​

-- ... Or just those employees that exist in another collection

-- t_emp intersect with t_emp_leaders;

​

68 of 113

MULTISET operations

69 of 113

nested tables ONLY

70 of 113

BLUE

RED

71 of 113

DECLARE

TYPE colours IS TABLE OF VARCHAR2(64);

comp1 colours;

comp2 colours;

result colours;

​

PROCEDURE show_table (p_table IN colours) IS

BEGIN

FOR i IN p_table.FIRST..p_table.LAST LOOP

DBMS_OUTPUT.PUT (p_table(i)||' ');

END LOOP;

DBMS_OUTPUT.NEW_LINE;

END;

BEGIN

comp1 := colours('Black','White','Red','Red');

comp2 := colours('Black','Red','Yellow');

​

result := comp1 MULTISET UNION comp2;

dbms_output.put_line ('multiset union is ');

show_table(result);

​

END;

/

​

multiset union is

Black White Red Red Black Red Yellow

72 of 113

...

BEGIN

comp1 := colours('Black','White','Red','Red');

comp2 := colours('Black','Red','Yellow');

​

result := comp1 MULTISET UNION DISTINCT comp2;

dbms_output.put_line ('multiset union distinct is ');

show_table(result);

​

END;

/

​

multiset union distinct is

Black White Red Yellow

73 of 113

...

BEGIN

comp1 := colours('Black','White','Red','Red');

comp2 := colours('Black','Red','Yellow');

​

result := comp1 MULTISET INTERSECT DISTINCT comp2;

dbms_output.put_line ('multiset intersect distinct is ');

show_table(result);

​

END;

/

​

multiset intersect distinct is

Black Red

74 of 113

...

BEGIN

comp1 := colours('Black','White','Red','Red');

comp2 := colours('Black','Red','Yellow');

​

result := comp1 MULTISET EXCEPT DISTINCT comp2;

dbms_output.put_line ('multiset except distinct is ');

show_table(result);

​

END;

/

​

multiset except distinct is

White

75 of 113

...

BEGIN

comp1 := colours('Black','White','Red','Red');

comp1 := comp1 MULTISET UNION DISTINCT comp1;

dbms_output.put_line (‘self multiset intersect distinct is ');

show_table(comp1);

​

END;

/

​

self multiset union distinct is

Black White Red

76 of 113

DECLARE

TYPE colours IS TABLE OF VARCHAR2(64);

t_colours colours := colours('Black','White','Red','Red');

​

PROCEDURE show_table (p_table IN colours)

...

BEGIN

DBMS_OUTPUT.PUT_LINE('Count:'||CARDINALITY(t_colours));

DBMS_OUTPUT.PUT_LINE('Distinct:'||CARDINALITY(SET(t_colours)));

IF t_colours IS NOT A SET THEN

t_colours := SET(t_colours);

show_table(t_colours);

END IF;

END;

/

​

Count:4

Distinct:3

Black White Red

77 of 113

but wait, there's still more...

78 of 113

Equality

DECLARE

TYPE colours IS TABLE OF VARCHAR2 (64);

group1 colours := colours ('Black', 'White');

group2 colours := colours ('White', 'Black');

group3 colours := colours ('White', 'Red');

BEGIN

IF group1 = group2 THEN

DBMS_OUTPUT.PUT_LINE ('Group 1 = Group 2');

ELSE

DBMS_OUTPUT.PUT_LINE ('Group 1 != Group 2');

END IF;

​

IF group2 != group3 THEN

DBMS_OUTPUT.PUT_LINE ('Group 2 != Group 3');

ELSE

DBMS_OUTPUT.PUT_LINE ('Group 2 = Group 3');

END IF;

END;

/

79 of 113

Membership

DECLARE

TYPE colours IS TABLE OF VARCHAR2(64);

t_colours colours := colours('Black','White','Red','Red');

v_colour VARCHAR2(64) := 'Black';

BEGIN

IF v_colour MEMBER OF t_colours THEN

DBMS_OUTPUT.PUT_LINE('Exists');

ELSE

DBMS_OUTPUT.PUT_LINE(‘Absent');

END IF;

END;

/

​

Exists

80 of 113

Subset

DECLARE

TYPE colours IS TABLE OF VARCHAR2(64);

t_colours colours := colours('Black','White','Red','Yellow');

t_colours2 colours := colours('Black','Red');

t_colours3 colours := colours('Black','Blue');

BEGIN

IF t_colours2 SUBMULTISET OF t_colours THEN

DBMS_OUTPUT.PUT_LINE('2 Subset');

ELSE

DBMS_OUTPUT.PUT_LINE('2 Separate');

END IF;

​

IF t_colours3 SUBMULTISET OF t_colours THEN

DBMS_OUTPUT.PUT_LINE('3 Subset');

ELSE

DBMS_OUTPUT.PUT_LINE('3 Separate');

END IF;

END;

/

​

2 Subset

3 Separate

81 of 113

82 of 113

Boom!

DECLARE

TYPE t_c IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;

l_c t_c;

BEGIN

SELECT LPAD('x',32767,'x') bb

BULK COLLECT

INTO l_c

FROM DUAL

CONNECT BY LEVEL <=1000000;

END;

/

declare

*

ERROR at line 1:

ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu call ,pl/sql vc2)

ORA-06512: at line 5

​

Elapsed: 00:00:02.70

83 of 113

limiting data

84 of 113

prevent collections from expanding with no limit

85 of 113

SELECT sal BULK COLLECT

INTO t_emp

FROM emp

WHERE ROWNUM <= 10;

​

WHERE ROWNUM BETWEEN 11 AND 20 – Won’t work

​

​

FROM emp SAMPLE(10) -- “Random”

​

-- Performance hit:

SELECT * FROM (

SELECT ROW_NUMBER() OVER (ORDER BY empno)

AS from_to_rank

…)

WHERE from_to_rank BETWEEN 1 AND 10

​

86 of 113

LIMIT clause

87 of 113

DECLARE

v_rows PLS_INTEGER := 10;

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp BULK COLLECT INTO t_emp LIMIT v_rows;

​

EXIT WHEN t_emp.COUNT = 0;

null; -- (Process information)

​

END LOOP;

CLOSE c_emp;

88 of 113

take care with the exit condition

89 of 113

OPEN c_emp;

LOOP

FETCH c_emp BULK COLLECT INTO t_emp LIMIT v_rows;

​

EXIT WHEN t_emp.COUNT = 0; -- Here?

null; -- (Process information)

EXIT WHEN c_emp%NOTFOUND; -- or here?

​

END LOOP;

CLOSE c_emp;

EXCEPTION WHEN NO_DATA_FOUND

-- Does this get raised?

90 of 113

t_emp.COUNT = 0

** Count:10

CLARK

JAMES

MARTIN

MILLER

WARD

KING

ALLEN

ADAMS

SMITH

JONES

** Count:4

TURNER

BLAKE

SCOTT

FORD

** Count:0

​

PL/SQL procedure successfully completed.

LOOP

FETCH c_emp BULK COLLECT INTO t_emp LIMIT 10;

dbms_output.put_line('** Count:'||t_emp.COUNT);

​

EXIT WHEN t_emp.COUNT = 0;

dbms_output.put_line(t_emp(i).ename);

-- EXIT WHEN c_emp%NOTFOUND;

​

91 of 113

WHEN c_emp%NOTFOUND

** Count:10

CLARK

JAMES

MARTIN

MILLER

WARD

KING

ALLEN

ADAMS

SMITH

JONES

** Count:4

TURNER

BLAKE

SCOTT

FORD

​

PL/SQL procedure successfully completed.

LOOP

FETCH c_emp BULK COLLECT INTO t_emp LIMIT 10;

dbms_output.put_line('** Count:'||t_emp.COUNT);

​

-- EXIT WHEN t_emp.COUNT = 0;

dbms_output.put_line(t_emp(i).ename);

EXIT WHEN c_emp%NOTFOUND;

​

92 of 113

implicit cursors don’t bulk up

93 of 113

DECLARE

TYPE emp_tab IS TABLE OF emp.empno%TYPE INDEX BY PLS_INTEGER;

t_emp emp_tab;

BEGIN

SELECT empno

BULK COLLECT INTO t_emp

FROM emp

LIMIT 10;

END;

/

LIMIT 10

*

ERROR at line 8:

ORA-06550: line 8, column 9:

PL/SQL: ORA-00933: SQL command not properly ended

ORA-06550: line 5, column 3:

PL/SQL: SQL Statement ignored

94 of 113

and if I have no data?

95 of 113

NO_DATA_FOUND

DECLARE

v_sal emp.sal%TYPE;

BEGIN

SELECT sal

INTO v_sal

FROM emp

WHERE 1=0;

END;

/

DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 4

​

DECLARE

TYPE emp_tab IS TABLE OF emp.sal%TYPE

INDEX BY PLS_INTEGER;

t_emp emp_tab;

BEGIN

SELECT sal BULK COLLECT

INTO t_emp

FROM emp

WHERE 1=0;

END;

/

PL/SQL procedure successfully completed.

96 of 113

bulk attributes

97 of 113

Bulk Cursor Attributes

DECLARE

TYPE dept_list IS TABLE OF NUMBER;

t_dept dept_list := dept_list(10, 20, 30);

BEGIN

FORALL i IN t_dept.FIRST..t_dept.LAST

DELETE FROM emp WHERE deptno = t_dept(i);

-- Each indice may update multiple rows

dbms_output.put_line('Total rows affected: '||SQL%ROWCOUNT);

-- How many rows were affected by each delete statement?

FOR j IN t_dept.FIRST..t_dept.LAST LOOP

dbms_output.put_line('Dept '||t_dept(j)||

'rows:'||SQL%BULK_ROWCOUNT(j));

END LOOP;

END;

/

​

Total rows affected: 14

Dept 10 rows:3

Dept 20 rows:5

Dept 30 rows:6

​

PL/SQL procedure successfully completed.

98 of 113

what if we have exceptions for a particular row?

99 of 113

CREATE TABLE emp2 AS SELECT * FROM emp;

ALTER TABLE emp2 ADD CONSTRAINT be_test CHECK (sal = ROUND(sal));

​

DECLARE

TYPE emp_list IS TABLE OF NUMBER;

t_emp emp_list := emp_list(7934, 7782, 7839, 7788, 7900);

e_dml_errors EXCEPTION;

PRAGMA exception_init(e_dml_errors, -24381);

​

BEGIN

FORALL i IN t_emp.FIRST..t_emp.LAST SAVE EXCEPTIONS

-- Will raise exception for sal of 1250 but not 1300

UPDATE emp2

SET sal = sal/100

WHERE empno = t_emp(i);

dbms_output.put_line('Total rows affected: ‘

||SQL%ROWCOUNT);

EXCEPTION

WHEN e_dml_errors THEN -- Now we figure out what failed and why.

dbms_output.put_line('Total failed updates: '||SQL%BULK_EXCEPTIONS.COUNT);

FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

dbms_output.put_line('Error '||j||' for indice ‘

||SQL%BULK_EXCEPTIONS(j).ERROR_INDEX);

dbms_output.put_line('Error message is ‘

||SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));

END LOOP;

END;

​

Total failed updates: 2

Error 1 for indice 2

Error message is ORA-02290: check constraint (.) violated

Error 2 for indice 5

Error message is ORA-02290: check constraint (.) violated

​

PL/SQL procedure successfully completed.

100 of 113

Bulk Exceptions

select empno, ename, sal from emp2 where empno in (7934, 7782, 7839, 7788, 7900);

EMPNO ENAME SAL

---------- ---------- ----------

7934 MILLER 13

7782 CLARK 2450

7839 KING 50

7788 SCOTT 30

7900 JAMES 950

​

5 rows selected.

101 of 113

102 of 113

103 of 113

Enterprise Manager

Limit 100 vs 1000

Separate runs of

For | Cursor | Collect | Limit | IISS

104 of 113

What’s new?

  • 8i
    • Bulk operations!
    • Collections indexed by BINARY_INTEGER only

​

  • 9i
    • Bulk collect via dynamically opened ref cursor

​

  • 9i Release 2
    • Bulk collect into collection of records
    • Index by VARCHAR2 & BINARY_INTEGER (or their subtypes)

​

  • 10g
    • PL/SQL engine may decide to peform array fetching for you
    • INDICES OF/VALUES OF introduced
    • MULTISET operations

​

  • 11g
    • table(bulk_index).field is now supported at run-time
    • DBMS_SQL allows bulk binds using user-define collection types

​

​

105 of 113

CREATE TABLE emp2 AS SELECT empno,ename,sal, SYSTIMESTAMP ts

FROM emp WHERE 1=0;

DECLARE

CURSOR sw IS SELECT empno, ename, sal FROM emp;

TYPE t_sw IS TABLE OF sw%ROWTYPE;

lt_sw t_sw;

BEGIN

OPEN sw;

FETCH sw BULK COLLECT INTO lt_sw;

CLOSE sw;

FORALL i IN lt_sw.FIRST..lt_sw.LAST

INSERT INTO emp2

VALUES (lt_sw(i).empno

,lt_sw(i).ename

,lt_sw(i).sal

,SYSTIMESTAMP);

END;

/

​

values (lt_sw(i).empno

*

ERROR at line 11:

ORA-06550: line 11, column 9:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

106 of 113

some final words

107 of 113

Read appropriate documentation

108 of 113

READ THIS… TWICE!

  • Tom Kyte

​

    • “If any paper you read tells you what but not why, regardless of its timestamp, I suggest you take its advice with a big grain of salt as it may be applicable only to only specific cases and not applicable to you at all.  In fact, it could be something that was true in the past and not true anymore.  If they give you no way to find out, ignore it.”

​

109 of 113

things change

110 of 113

it only takes one case to show something is...

111 of 113

not universally true

112 of 113

Rules of thumb without evidence, without ways to test them before implementing them, are dangerous, seriously dangerous

“Because a rule of thumb is a principle with broad application not intended to be strictly accurate or reliable for all situations”

​

113 of 113

SAGE Computing Services

Customised Oracle Training Workshops and Consulting

​

​

​

​

​

​

Questions and Answers?

​

​

​

​

​

Presentations are available from our website:

http://www.sagecomputing.com.au

​

enquiries@sagecomputing.com.au

scott.wesley@sagecomputing.com.au

http://triangle-circle-square.blogspot.com

​

​

​

​

​

​