Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT -not getting expected result (oracle 10g,windows 7)
| BULK COLLECT -not getting expected result [message #618009] |
Sun, 06 July 2014 15:11  |
 |
harishankar_kar
Messages: 22
Registered: July 2014
Location: India
|
Junior Member |
|
|
Hi ,
I have a table called test_input and another table called test_output.
I have some records in test_input and want to insert the same into test_output.
The only change is that in test_output the name column is having less size than test_input ( name column ).
I am trying to get the exceptions using SAVE EXCEPTION method.The DDL and Insert statements are as follows .
CREATE TABLE TEST_INPUT
(
NAME VARCHAR2(10 BYTE),
ID NUMBER,
CITY VARCHAR2(15 BYTE),
PINCODE NUMBER
)
CREATE TABLE SYS.TEST_OUTPUT
(
NAME VARCHAR2(4 BYTE),
ID NUMBER,
CITY VARCHAR2(15 BYTE)
)
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('hari', 1, 'hyd', 500081);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('sumati', 2, 'hyd', 500081);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('ashishmi', 3, 'bng', 767001);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('lil', 4, 'hyd', 500081);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('kill', 5, 'hyd', 500081);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('arunbehera', 6, 'bam', 560065);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('arbind', 7, 'hyd', 500081);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('ashokpatro', 8, 'hyd', 500081);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('silu', 9, 'bam', 7893325);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('asim', 10, 'hyd', 500081);
Insert into TEST_INPUT
(NAME, ID, CITY, PINCODE)
Values
('santoshb', 11, 'tal', 500081);
COMMIT;
declare
cursor name_id_city is
select name,id,city from test_input;
type emp_nameidcity_t is table of name_id_city%ROWTYPE INDEX BY PLS_INTEGER;
l_emp_id emp_nameidcity_t;
BEGIN
open name_id_city;
loop
fetch name_id_city bulk collect into l_emp_id;
exit when l_emp_id.COUNT=0;
end loop;
close name_id_city;
FORALL indx in 1..l_emp_id.COUNT SAVE EXCEPTIONS
insert into test_output values l_emp_id(indx);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Inside exception');
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ':'
|| SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
END LOOP;
END;
after executing this code when i am trying to see the test_output , there are no records and moreover it's not
going into exception state .
Please suggest on this .
*BlackSwan made correct {code} tags. Please do so yourself in the future.
http://www.orafaq.com/forum/t/174502/102589/
[Updated on: Sun, 06 July 2014 15:19] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: BULK COLLECT -not getting expected result [message #618017 is a reply to message #618009] |
Sun, 06 July 2014 15:43  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Get rid of LOOP. Look at:
loop
fetch name_id_city bulk collect into l_emp_id;
exit when l_emp_id.COUNT=0;
end loop;
1. Fetch will intialize l_emp_id and fetch all rows into l_emp_id.
2. exit will check if l_emp_id.count = 0, which will be FALSE so it will continue back to fetch.
3. Fetch will initialize l_emp_id and since all rows were fetched already will fetch no rows.
4. exit will check if l_emp_id.count = 0, which will be TRUE and will exit loop
Now you endup with empty l_emp_id:
SQL> set serveroutput on
SQL> declare
2 cursor name_id_city is
3 select name,id,city from test_input;
4 type emp_nameidcity_t is table of name_id_city%ROWTYPE INDEX BY PLS_INTEGER;
5 l_emp_id emp_nameidcity_t;
6
7 BEGIN
8
9 open name_id_city;
10 loop
11 fetch name_id_city bulk collect into l_emp_id;
12 exit when l_emp_id.COUNT=0;
13 end loop;
14 close name_id_city;
15 FORALL indx in 1..l_emp_id.COUNT SAVE EXCEPTIONS
16 insert into test_output values l_emp_id(indx);
17 EXCEPTION
18 WHEN OTHERS
19 THEN
20 DBMS_OUTPUT.put_line('Inside exception');
21 FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
22 LOOP
23 DBMS_OUTPUT.put_line (
24 SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
25 || ':'
26 || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
27 END LOOP;
28 END;
29 /
PL/SQL procedure successfully completed.
SQL> select * from test_output;
no rows selected
SQL>
And after removing loop:
SQL> set serveroutput on
SQL> declare
2 cursor name_id_city is
3 select name,id,city from test_input;
4 type emp_nameidcity_t is table of name_id_city%ROWTYPE INDEX BY PLS_INTEGER;
5 l_emp_id emp_nameidcity_t;
6
7 BEGIN
8
9 open name_id_city;
10 fetch name_id_city bulk collect into l_emp_id;
11 close name_id_city;
12 FORALL indx in 1..l_emp_id.COUNT SAVE EXCEPTIONS
13 insert into test_output values l_emp_id(indx);
14 EXCEPTION
15 WHEN OTHERS
16 THEN
17 DBMS_OUTPUT.put_line('Inside exception');
18 FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
19 LOOP
20 DBMS_OUTPUT.put_line (
21 SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
22 || ':'
23 || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
24 END LOOP;
25 END;
26 /
Inside exception
2:12899
3:12899
6:12899
7:12899
8:12899
11:12899
PL/SQL procedure successfully completed.
SQL> select * from test_output;
NAME ID CITY
---- ---------- ---------------
hari 1 hyd
lil 4 hyd
kill 5 hyd
silu 9 bam
asim 10 hyd
SQL>
SY.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 07:21:13 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
