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.
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 /
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.
5 responses so far ↓
1 Veeresh // 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
Leave a Comment