Home » SQL & PL/SQL » SQL & PL/SQL » Problem in BULK COLLECT and FORALL
Problem in BULK COLLECT and FORALL [message #174639] Tue, 30 May 2006 02:57 Go to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
Dear All,

I have used BULK COLLECT and FORALL in Packages.
For your kind information my Source Table is having 12 Million Records. Here is the sample script.


TYPE ACCOUNT IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
      Accountid ACCOUNT;


OPEN temp_load_cur;
	LOOP
		FETCH temp_load_cur BULK COLLECT 
                INTO Accountid LIMIT 1000;
		
                FORALL 	i IN 1.. Accountid.COUNT
			INSERT INTO DONOR_TEMP
			VALUES
			(
			SEQ_DONOR_DIM_KEY.NEXTVAL,
			Accountid(i)
			);
		COMMIT;
		EXIT WHEN temp_load_cur%NOTFOUND;
	END LOOP;
CLOSE temp_load_cur;


The above Script is working fine. It is fetching 1000 records and
it is inserting in to DONOR_TEMP table. when it is loading data in to DONOR_TEMP table if i execute countinously

SELECT COUNT(*) FROM DONOR_TEMP;

I can able to see the difference. eg when i execute first time it will give 1000, second time 2000, third time 3000.... It will keep on varry...

My Problem is
-------------

I applied the same logic with little validation...
The script is below
TYPE NUMBER_TABLE_DONOR IS TABLE OF DONOR_DIM%ROWTYPE
	INDEX BY BINARY_INTEGER;
	Donor_Dim_Table_Orig		NUMBER_TABLE_DONOR;
	Donor_Dim_Table_Temp		NUMBER_TABLE_DONOR;


OPEN c_rec_type_3;
	LOOP
          l_cnt :=1;
	  Donor_Dim_Table_Temp.DELETE;
	  Donor_Dim_Table_Orig.DELETE;	

          FETCH c_rec_type_3 BULK COLLECT INTO 
	  Donor_Dim_Table_Temp LIMIT 1000;

          FOR I IN 1..Donor_Dim_Table_Temp.COUNT 
	  LOOP
	       l_err_flag 	:= 'Y';
               BEGIN			
			IF Donor_Dim_Table_Temp(i).ACCT_TYPE_CD IS NULL 
			THEN
				RAISE EXC_ACCT_TYPE_NULL;
			END IF;

			EXCEPTION
			WHEN EXC_ACCT_TYPE_NULL THEN
				l_err_flag 	:= 'N';
				l_err_msg	:= ' Account Type is NULL for this ACCOUNID '||Donor_Dim_Table_Temp(i).donor_acct_id;
				INSERT INTO
				SDV_ERROR_INFO
				(
				PROG_ID, 
				ERR_SRL_NUM, 
				SRC_ID, 
				ERR_TIME, 
				ERR_SRC_KEY_COL_NAME_1, 
				ERR_SRC_KEY_COL_VAL_1, 
				ERR_DETAILS
				)	
				VALUES
				(
				l_prog_id,
				SEQ_SDV_ERROR_INFO_KEY.NEXTVAL,
				l_src_id,
				SYSDATE,
				'ACCOUNTID',
				Donor_Dim_Table_Temp(i).donor_acct_id,
				l_err_msg
				);
			END;

                        --like this i have 10 validation.....

                        --finally

        IF l_err_flag = 'Y'
  	THEN
	Donor_Dim_Table_Orig(l_cnt) := Donor_Dim_Table_Temp(i);
	l_cnt := l_cnt + 1;
	END IF;         
        END LOOP;

        FORALL i IN 1..Donor_Dim_Table_Orig.COUNT	
		INSERT INTO DONOR_DIM VALUES 
		Donor_Dim_Table_Orig(i);
 		 
		COMMIT;	
     	EXIT WHEN c_rec_type_3%NOTFOUND;
    END LOOP;
CLOSE c_rec_type_3;


while loading data into DONOR_DIM table if execute this
SELECT COUNT(*) FROM DONOR_DIM;

it is giving result as 0 for 14 hrs. After 14 hrs only it starts inserting records. Why this is happening here?

But the same logic working fine in the above script (without validation).

As per my logic i need to move 1000 records to a PL/SQL Table(Donor_Dim_Table_Temp) and i will validate the records if it is valid then i will move that records to another PL/SQL Table (Donor_Dim_Table_Orig).

Then immd i am inserting that valid records (Donor_Dim_Table_Orig) into DONOR_DIM table. After this i will delete these 1000 records from both the PL/SQL Table then i will start fetching next 1000 records.

Please tell me why this is happening ?

If this is not clear please let me know i will attach my package spec and package body.

Regards,
Prabhu


Regards,
Prabhu
Re: Problem in BULK COLLECT and FORALL [message #190214 is a reply to message #174639] Tue, 29 August 2006 14:23 Go to previous message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

I would be better if u can attach your complete pack.

Though I have gone through your code it needs fine tuning else its ok. Please mention your oracle verson also.


RP Singh
Previous Topic: ORA 01555
Next Topic: Compare sequences in two tables to find missing sequence no
Goto Forum:
  


Current Time: Thu Dec 08 02:07:46 CST 2016

Total time taken to generate the page: 0.11155 seconds