I just got a question on asktom recently. To paraphrase, the question is basically:
We copy some data from another table. After the copy, some of the data is missing. When we check the source table, the data is there. What can be the reason for this????
Well, here is the code - after you read the code, I want you to GUESS as fast as possible what the problem PROBABLY IS. If you are a long time reader, I hope you get it "fast" :) My reply is here, but take the challenge first. Please, ignore the really bad "transaction processing" - yes it is *part* of the problem, but try to guess really fast why this code is most likely BROKEN without any visible errors.
CREATE OR REPLACE PROCEDURE "SIEBEL_CUST"
CURSOR siebel_cust_cur IS
WHERE account_id NOT IN (SELECT cust_account_id
TYPE icust_tabarr IS TABLE OF customerdata_vw%ROWTYPE INDEX BY BINARY_INTEGER;
insert_count NUMBER := 1;
i NUMBER := 1;
file_id := utl_file.FOPEN( 'h:\nidbatchprog', 'ston-c.log', 'w' );
EXIT WHEN siebel_cust_cur%NOTFOUND ;
FETCH siebel_cust_cur INTO icustarr(i);
INSERT INTO CUSTOMERS (customer_id,cust_account_id,customer_name)
i := i + 1;
insert_count :=insert_count + 1;
IF insert_count = 1000 THEN
insert_count := 1;
insert_count := insert_count + 1;
utl_file.PUT_LINE( file_id,'Customer migration completed');
WHEN OTHERS THEN NULL;