Home » SQL & PL/SQL » SQL & PL/SQL » Avoid Duplicate using FORALL
| Avoid Duplicate using FORALL [message #49414] |
Mon, 07 February 2005 21:57  |
bhagwan
Messages: 86
Registered: September 2004
|
Member |
|
|
Hi,
Below script is just a snapshot of my table which I have simplified for your all reference.
I want to basically use BULK COLLECT and FORALL statement to populate Test_Table2 with Unique Data Only.
Oracle version:8.1.7
**************** START **********************
Source Table Script
CREATE TABLE TEST_TABLE1
(
ID NUMBER NOT NULL,
NAME VARCHAR2(30)
)
INSERT INTO TEST_TABLE1 ( ID, NAME ) VALUES ( 10, 'ABEY');
INSERT INTO TEST_TABLE1 ( ID, NAME ) VALUES ( 10, 'STEVE');
INSERT INTO TEST_TABLE1 ( ID, NAME ) VALUES ( 20, 'BECK');
INSERT INTO TEST_TABLE1 ( ID, NAME ) VALUES ( 30, 'KATTY');
INSERT INTO TEST_TABLE1 ( ID, NAME ) VALUES ( 40, 'ANDREW');
INSERT INTO TEST_TABLE1 ( ID, NAME ) VALUES ( 40, 'JOHN');
Note: My Source Table has duplicate ID's
Target Table Create Script
CREATE TABLE TEST_TABLE2
(
ID NUMBER,
NAME VARCHAR2(30)
)
ALTER TABLE TEST_TABLE2 ADD (CONSTRAINT PK_TEST_TABLE2 PRIMARY KEY (ID))
**************** END **********************
I first tested on a small PL-SQL Block which works fine.i.e. Iam able to only insert
Unique record into authors table.This was done by defining an infinite LOOP-END LOOP
Outside FORALL Statement.
DECLARE
TYPE NameList IS TABLE OF authors.name%type;
name_tab NameList := NameList('Dawes'
,'Pribyl','Feuerstein','Gennick'
,'Pribyl','Beresniewicz','Dawes','Dye');
l_name_tab NameList := NameList('Dawes'
,'Pribyl','Feuerstein','Gennick'
,'Pribyl','Beresniewicz','Dawes','Dye');
l_start number := 1;
BEGIN
LOOP
BEGIN
FORALL indx IN l_start..name_tab.COUNT
INSERT INTO authors (name) VALUES (name_tab(indx));
EXIT;
-- authors has pk index on name
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error at row no = ' || (l_start+sql%rowcount) ||' ' || sqlerrm );
l_start := l_start + sql%rowcount + 1;
dbms_output.put_line('Affected row value is = ' ||l_name_tab(l_start-1));
END;
END LOOP;
END;
Next, when Iam trying to implement same functionality using BULK COLLECT and FORALL together, Iam not able to
get the desired output as my program fails.Below is my package.
******** PACKAGE SPECIFICATION *************
CREATE OR REPLACE PACKAGE DELME AS
type a is table of number;
type b is table of varchar2(30);
PROCEDURE get_bulkcollect;
PROCEDURE Prc_Forall_Insert(p_objA IN DELME.a, p_objB IN DELME.b);
END;
******** PACKAGE BODY *************
CREATE OR REPLACE PACKAGE BODY delme
AS
PROCEDURE get_bulkcollect
AS
obja a;
objb b;
CURSOR c1
IS SELECT ID, NAME FROM test_table1;
BEGIN
OPEN c1;
FETCH c1
BULK COLLECT INTO obja, objb;
CLOSE c1;
IF obja.COUNT > 0
THEN
prc_forall_insert (obja, objb);
END IF;
END get_bulkcollect;
PROCEDURE prc_forall_insert (p_obja IN delme.a, p_objb IN delme.b)
AS
l_start NUMBER := 1;
BEGIN
LOOP
BEGIN
FORALL i IN l_start .. p_obja.COUNT
INSERT INTO test_table2(ID, NAME) VALUES (p_obja (i), p_objb (i));
EXIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END prc_forall_insert;
END delme;
/
******** PACKAGE BODY *************
My Output in Test_Table2 should be finally..
ID Name
10 ABEY
20 BECK
30 KATTY
40 ANDREW
Note: STEVE and JOHN have been ignored.
Can someone guide me?
Regards,
Bhagwan
|
|
|
|
|
|
| Re: Avoid Duplicate using FORALL [message #49423 is a reply to message #49414] |
Mon, 07 February 2005 23:48  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
When others then null
NEVER NEVER do that; it is a bug waiting to happen. You say: if there is something wrong, don't tell me, just go on doing whatever you want to.
Second: why the loop statement in the forall-procedure? Because of your great exception handler, the exit statement is never reached. The forall-insert fails, causing the when others then null statement to be executed. Hence, you created an infinite loop.
Get rid of the loop.
Get rid of the exception handler.
Use the following statement:
FORALL i IN l_start .. p_obja.COUNT <b>save exceptions</b>
INSERT INTO test_table2(ID, NAME) VALUES (p_obja (i), p_objb (i));
There will be a pl/sql table called SQL%BULK_EXCEPTIONS that will be filled with the errors that occurred during the forall insert.
Columns in this table that are of interest:
ERROR_INDEX: index no of p_obja that raised an error
ERROR_CODE: ... indeed ;)
See here for an example
hth
|
|
|
|
| Re: Avoid Duplicate using FORALL [message #49425 is a reply to message #49423] |
Tue, 08 February 2005 00:28 |
bhagwan
Messages: 86
Registered: September 2004
|
Member |
|
|
Frank,
Iam using Oracle 8.1.7 so 'SAVE EXCEPTIONS' is not allowed thats why Iam using an infinite loop outside forall statement.
I got this solution from 'AskTom' Website and you can see that my simple pl-sql block is implementing this functionality and works fine.
DECLARE
TYPE NameList IS TABLE OF authors.name%type;
name_tab NameList := NameList('Dawes'
,'Pribyl','Feuerstein','Gennick'
,'Pribyl','Beresniewicz','Dawes','Dye');
l_name_tab NameList := NameList('Dawes'
,'Pribyl','Feuerstein','Gennick'
,'Pribyl','Beresniewicz','Dawes','Dye');
l_start number := 1;
BEGIN
LOOP
BEGIN
FORALL indx IN l_start..name_tab.COUNT
INSERT INTO authors (name) VALUES (name_tab(indx));
EXIT;
-- authors has pk index on name
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error at row no = ' || (l_start+sql%rowcount) ||' ' || sqlerrm );
l_start := l_start + sql%rowcount + 1;
dbms_output.put_line('Affected row value is = ' ||l_name_tab(l_start-1));
END;
END LOOP;
END;
Only Iam not able to use this feature in my package delme which I have pasted.
Please note Oracle version is NOT 9i.
Regards,
Bhagwan
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 05 03:42:00 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
