| Error in Bulk Collect [message #10522] |
Wed, 28 January 2004 02:37  |
Manoj
Messages: 101
Registered: August 2000
|
Senior Member |
|
|
We want to use record and Index by Table in Bulk collect, it show error.
DECLARE
TYPE LOAD_REC IS RECORD (
HS_SEQ_CODE EMP.EMPNO%TYPE,
HS_SAL EMP.SAL%TYPE
);
TYPE LOAD_TAB IS TABLE OF LOAD_REC
INDEX BY BINARY_INTEGER;
LOAD_DATA_TAB LOAD_TAB ;
BULK_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(BULK_ERRORS,-24381);
BEGIN
SELECT EMPNO,SAL
BULK COLLECT INTO LOAD_DATA_TAB
FROM EMP;
FORALL I IN LOAD_DATA_TAB.FIRST..LOAD_DATA_TAB.LAST --SAVE EXCEPTIONS
INSERT INTO TRY(empno,sal) VALUES LOAD_DATA_TAB(I);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
It will show following Error :
HS_SAL EMP.SAL%TYPE
*
ERROR at line 5:
ORA-06550: line 25, column 31:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 25, column 4:
PL/SQL: SQL Statement ignored
Can any on help me but this is mendatory use this record and Table . Because there is a to column and i want to insert data in 2 column.
Regards
Manoj Kumar
|
|
|
|
| Re: Error in Bulk Collect [message #10524 is a reply to message #10522] |
Wed, 28 January 2004 08:21  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The number of columns in your target table must match the number of columns in your record and you cannot specify a column list on the insert statement (since, by rule, you have to supply values for all columns on a bulk bind).
sql>desc try
Name Null? Type
---------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
SAL NUMBER(7,2)
sql>declare
2 type load_rec is record (xempno emp.empno%type, xsal emp.sal%type);
3 type load_tab is table of load_rec index by pls_integer;
4 load_data_tab load_tab ;
5 begin
6 select empno, sal bulk collect into load_data_tab from emp where rownum <= 3;
7 forall i in load_data_tab.first..load_data_tab.last
8 insert into try <b>(empno, sal)</b> values load_data_tab(i);
9 end;
10 /
type load_tab is table of load_rec index by pls_integer;
*
ERROR at line 3:
ORA-06550: line 8, column 34:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored
sql>declare
2 type load_rec is record (xempno emp.empno%type, xsal emp.sal%type);
3 type load_tab is table of load_rec index by pls_integer;
4 load_data_tab load_tab ;
5 begin
6 select empno, sal bulk collect into load_data_tab from emp where rownum <= 3;
7 forall i in load_data_tab.first..load_data_tab.last
8 insert into try values load_data_tab(i);
9 end;
10 /
PL/SQL procedure successfully completed.
sql>select * from try;
EMPNO SAL
--------- ---------
7369 800
7499 1600
7521 1250
3 rows selected.
sql>alter table try add new_column int;
Table altered.
sql>declare
2 type load_rec is record (xempno emp.empno%type, xsal emp.sal%type);
3 type load_tab is table of load_rec index by pls_integer;
4 load_data_tab load_tab ;
5 begin
6 select empno, sal bulk collect into load_data_tab from emp where rownum <= 3;
7 forall i in load_data_tab.first..load_data_tab.last
8 insert into try values load_data_tab(i);
9 end;
10 /
insert into try values load_data_tab(i);
*
ERROR at line 8:
ORA-06550: line 8, column 17:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored |
|
|
|
| Re: Error in Bulk Collect [message #10529 is a reply to message #10524] |
Wed, 28 January 2004 19:29 |
Manoj
Messages: 101
Registered: August 2000
|
Senior Member |
|
|
Thanks for Reply. but dear in my table there is 50 columns and i want to insert only 10 columns then how it is work. either i have to declare a 50 columns record set and select null into bulk then but is the benifite of record and index by table.
Thanx and Regards
Manoj Kumar
|
|
|
|
| Re: Error in Bulk Collect [message #10537 is a reply to message #10529] |
Wed, 28 January 2004 23:25 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use a FOR LOOP instead of FORALL. However, unless there is something else to justify this process, just an insert would probably be faster. Please see the examples below.
scott@ORA92> DESC try
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NUMBER(4)
SAL NUMBER(7,2)
NEW_COLUMN NUMBER(38)
scott@ORA92> -- with for loop instead of forall:
scott@ORA92> DECLARE
2 TYPE load_rec IS RECORD
3 (xempno emp.empno%TYPE,
4 xsal emp.sal%TYPE);
5 TYPE load_tab IS TABLE OF load_rec INDEX BY PLS_INTEGER;
6 load_data_tab load_tab;
7 BEGIN
8 SELECT empno, sal BULK COLLECT INTO load_data_tab FROM emp WHERE ROWNUM <= 3;
9 FOR i IN load_data_tab.FIRST..load_data_tab.LAST
10 LOOP
11 INSERT INTO try (empno, sal)
12 VALUES (load_data_tab(i).xempno, load_data_tab(i).xsal);
13 END LOOP;
14 END;
15 /
PL/SQL procedure successfully completed.
scott@ORA92> SELECT * FROM try
2 /
EMPNO SAL NEW_COLUMN
---------- ---------- ----------
7369 800
7499 1600
7521 1250
scott@ORA92> TRUNCATE TABLE try
2 /
Table truncated.
scott@ORA92> -- insert would probably be faster:
scott@ORA92> INSERT INTO try (empno, sal) SELECT empno, sal FROM emp WHERE ROWNUM <= 3
2 /
3 rows created.
scott@ORA92> SELECT * FROM try
2 /
EMPNO SAL NEW_COLUMN
---------- ---------- ----------
7369 800
7499 1600
7521 1250
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
