SQL & PL/SQL

I'm in the early stages of trying to design a validation merge process and I'm trying to come up with different options to test based on the following requirements:

1. Customer provided flat file which is tab delimited
2. Flat file contents need to be validated first, and
3. Optionally merged with the existing record sets.
4. Validation must log why each record failed validation.

This is against Oracle DB 11.2.0.2. If I had to wager a guess as far as the volume of data it would be less than 40,000 records.

From my perspective I see the process being broken up into three separate phases: load, validate, (and optionally) merge.

I'm not concerned with the load portion as I'm probably going to use either SQL*Loader or External Tables to get the data into a staging area. If I must I'll consider UTL_FILE.

I am however concerned with an efficient validate and merge process. Requirement #4 above seems to indicate that a row by row review of the record set would be required. Any thoughts on a set based approach to this? I was thinking MERGE with LOG EXCEPTIONS but the error table wouldn't catch all the required errors and it is not exposed to the user for review.

Either way I thought I'd put some pseudo-code below for options that I have thought of and I figured you all might be able to tweak and/or come up with a better idea.

Slow-by-slow err Row-by-Row

FOR r IN (SELECT raw_data FROM staging_area)
LOOP
	parse_record;

	BEGIN
		validate_record;

		IF ( merge_records = TRUE )
		THEN
			MERGE INTO ...
		END IF;
	EXCEPTIONS
		WHEN error_a THEN
			LOG
		WHEN error_b THEN
			LOG
		...
	END;
END LOOP;

Load outside of loop / Validate outside of object type

DECLARE
	tbl IS TABLE OF	SOME_SQL_OBJECT_TYPE;
BEGIN
	FOR r IN (SELECT raw_data FROM staging_area)
	LOOP
		BEGIN
			validate_record;
			
			-- parse would happen in constructor of object type
			tbl(i) := SOME_SQL_OBJECT_TYPE(raw);
		EXCEPTIONS
			WHEN error_a THEN
				LOG
			WHEN error_b THEN
				LOG
			...
		END;	
	END LOOP;

	IF ( merge_records = TRUE )
	THEN
		MERGE INTO ...
		USING (
			 SELECT a, b, c FROM TABLE(tbl)
		       )
                ...
                ;
	END IF;
END;

Load outside of loop / Validate defined in object type constructor

DECLARE
	tbl IS TABLE OF	SOME_SQL_OBJECT_TYPE;
BEGIN
	FOR r IN (SELECT raw_data FROM staging_area)
	LOOP
		BEGIN
			-- parse and validate would happen in constructor of object type
			tbl(i) := SOME_SQL_OBJECT_TYPE(raw);
		EXCEPTIONS
			WHEN error_a THEN
				LOG
			WHEN error_b THEN
				LOG
			...
		END;	
	END LOOP;

	IF ( merge_records = TRUE )
	THEN
		MERGE INTO ...
		USING (
			 SELECT a, b, c FROM TABLE(tbl)
		       )
                ...
                ;
	END IF;
END;

Defer logging until after validation

DECLARE
	tbl IS TABLE OF	SOME_SQL_OBJECT_TYPE;
	bad_tbl IS TABLE OF SOME_OTHERSQL_OBJECT_TYPE;
BEGIN
	FOR r IN (SELECT raw_data FROM staging_area)
	LOOP
		BEGIN
			
			-- parse and validate would happen in constructor of object type
			tbl(i) := SOME_SQL_OBJECT_TYPE(raw);
		EXCEPTIONS
			WHEN error_a THEN
				bad_tbl(i) := ...;
			WHEN error_b THEN
				bad_tbl(i) := ...;
			...
		END;	
	END LOOP;

	FOR EACH bad_tbl
	LOOP
		log;
	END LOOP;

	IF ( merge_records = TRUE )
	THEN
		MERGE INTO ...
		USING (
			 SELECT a, b, c FROM TABLE(tbl)
		       )
                ...
                ;
	END IF;
END;

Thanks. Again I'm just trying to see what my options are before I go and test the various solutions.