PL/SQL::Improvement in BULK In-BIND table of records

September 27th, 2007 by Anand · 5 Comments

Previous 11g, if i want to refer the field of a ‘Table Of record’ with in FORALL, Oracle raises error as follows

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

Example:

SCOTT>SELECT banner
2Â FROM v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
COREÂ Â Â 10.2.0.1.0Â Â Â Â Â Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

SCOTT>DECLARE
2Â Â TYPE s_tab IS TABLE OF emp%ROWTYPE;
3Â Â s_array s_tab;
4Â BEGIN
5Â Â SELECT * BULK COLLECT
6Â Â INTO s_array
7Â Â FROM EMP
8Â Â WHERE comm IS NULL;
9
10Â Â FORALL i IN 1..s_array.COUNT
11Â Â UPDATE emp
12Â Â SET sal = 50000
13Â Â WHERE empno = s_array(i).empno;
14Â END;
15Â /
WHERE empno = s_array(i).empno;
*
ERROR at line 13:
ORA-06550: line 13, column 16:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 13, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 13, column 16:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 11, column 2:
PL/SQL: SQL Statement ignored

Now check out Oracle 11g

SCOTT>SELECT banner
2Â FROM v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
COREÂ Â Â 11.1.0.6.0Â Â Â Â Â Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production

SCOTT>SELECT * FROM emp WHERE comm IS NULL;

EMPNO ENAMEÂ Â Â Â Â JOBÂ Â Â Â Â Â Â Â Â Â Â Â Â MGR HIREDATEÂ Â Â Â Â Â Â Â SALÂ Â Â Â Â Â COMMÂ Â Â Â DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITHÂ Â Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7902 17-DEC-80Â Â Â Â Â Â Â 800Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7566 JONESÂ Â Â Â Â MANAGERÂ Â Â Â Â Â Â Â 7839 02-APR-81Â Â Â Â Â Â 2975Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7698 BLAKEÂ Â Â Â Â MANAGERÂ Â Â Â Â Â Â Â 7839 01-MAY-81Â Â Â Â Â Â 2850Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30
7782 CLARKÂ Â Â Â Â MANAGERÂ Â Â Â Â Â Â Â 7839 09-JUN-81Â Â Â Â Â Â 2450Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
7788 SCOTTÂ Â Â Â Â ANALYSTÂ Â Â Â Â Â Â Â 7566 19-APR-87Â Â Â Â Â Â 3000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7839 KINGÂ Â Â Â Â Â PRESIDENTÂ Â Â Â Â Â Â Â Â Â Â 17-NOV-81Â Â Â Â Â Â 5000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
7876 ADAMSÂ Â Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7788 23-MAY-87Â Â Â Â Â Â 1100Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7900 JAMESÂ Â Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7698 03-DEC-81Â Â Â Â Â Â Â 950Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30
7902 FORDÂ Â Â Â Â Â ANALYSTÂ Â Â Â Â Â Â Â 7566 03-DEC-81Â Â Â Â Â Â 3000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7934 MILLERÂ Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7782 23-JAN-82Â Â Â Â Â Â 1300Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10

10 rows selected.

PL/SQL procedure successfully completed.

SCOTT>SELECT * FROM emp WHERE comm IS NULL;

EMPNO ENAMEÂ Â Â Â Â JOBÂ Â Â Â Â Â Â Â Â Â Â Â Â MGR HIREDATEÂ Â Â Â Â Â Â Â SALÂ Â Â Â Â Â COMMÂ Â Â Â DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITHÂ Â Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7902 17-DEC-80Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7566 JONESÂ Â Â Â Â MANAGERÂ Â Â Â Â Â Â Â 7839 02-APR-81Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7698 BLAKEÂ Â Â Â Â MANAGERÂ Â Â Â Â Â Â Â 7839 01-MAY-81Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30
7782 CLARKÂ Â Â Â Â MANAGERÂ Â Â Â Â Â Â Â 7839 09-JUN-81Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
7788 SCOTTÂ Â Â Â Â ANALYSTÂ Â Â Â Â Â Â Â 7566 19-APR-87Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7839 KINGÂ Â Â Â Â Â PRESIDENTÂ Â Â Â Â Â Â Â Â Â Â 17-NOV-81Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
7876 ADAMSÂ Â Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7788 23-MAY-87Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7900 JAMESÂ Â Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7698 03-DEC-81Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 30
7902 FORDÂ Â Â Â Â Â ANALYSTÂ Â Â Â Â Â Â Â 7566 03-DEC-81Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 20
7934 MILLERÂ Â Â Â CLERKÂ Â Â Â Â Â Â Â Â Â 7782 23-JAN-82Â Â Â Â Â 50000Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10

10 rows selected.

Hide

Tags: Oracle 11g New Features

5 responses so far ↓

1 // Nov 23, 2007 at 4:35 pm

The BULK method of selecting records may take more time in a Clustered Data Base Systems(Spreaded database over multiple hard drives). I tried this method, I did not get expected results, rather It has taken more time than pure SQL method of reading bulk records. This method would work in a database mounted in a single hard drive.
2 Ray DeBruyn // Dec 6, 2007 at 5:37 am

There are no expected results posted here, so you must have an assumed or expected result. I don’t really see how bulk binding can be slower. I’d be interested to see exactly what code you ran, what your expectation was and what result you had.
3 Rajender Singh // Dec 14, 2007 at 12:05 am

Hi Veeresh,

I am also wondering why you didn’t get expected results.

Logically running whole thing as one SQL is always faster than running more than one SQL to achive same thing.

Regards,

Raj
4 Brijesh // Apr 8, 2008 at 3:01 pm

If this bulk bind solution in oracel 11g then how can we solve that type of problem in oracle 8i versrion
5 Rajender Singh // Apr 8, 2008 at 11:35 pm

Hi Brijesh,

Before 11g, I could find only following way to achieve the above solution due to restriction before 11g.

DECLARE
TYPE empRec IS RECORD
(
empno DBMS_SQL.NUMBER_TABLE,
ename DBMS_SQL.VARCHAR2_TABLE,
job DBMS_SQL.VARCHAR2_TABLE,
mgr DBMS_SQL.NUMBER_TABLE,
hiredate DBMS_SQL.DATE_TABLE,
sal DBMS_SQL.NUMBER_TABLE,
comm DBMS_SQL.NUMBER_TABLE,
deptno DBMS_SQL.NUMBER_TABLE
);

l_record empRec;
BEGIN
SELECT * BULK COLLECT
INTO l_record.empno, l_record.ename, l_record.job, l_record.mgr, l_record.hiredate, l_record.sal, l_record.comm, l_record.deptno
FROM EMP
WHERE comm IS NULL;

FORALL I IN 1 .. l_record.empno.count
UPDATE emp
set empno = l_record.empno(i),
ename = l_record.ename(i),
job = l_record.job(i),
mgr = l_record.mgr(i),
hiredate = l_record.hiredate(i),
sal = l_record.sal(i),
comm = l_record.comm(i),
deptno = l_record.deptno(i)
WHERE empno = l_record.empno(i);

END;

There is very nice discussion about this kind of problem at Ask Tom.

Check out following url:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2367352052686

Thanks & Regards,

Raj