Brad -
I banged the following off the top of my head; it should work (provided it compiles - now and again I've been known to miss a ';' here and there!). It's not real pretty, but I think it will do the job for you.
CREATE OR REPLACE PROCEDURE load_my_file(p_directory VARCHAR2, p_file_name VARCHAR2) AS
-- THIS PROCEDURE OPENS A COMMA-DELIMITED FILE AND UPDATES
-- THE COST AND SUPPLIER FIELDS OF A TABLE CALLED my_table.
-- IT TAKES TWO CHARACTER STRINGS AS ARGUMENTS: THE FIRST
-- IS THE FULL PATH TO THE FILE;THE SECOND IS THE FILE NAME.
-- FROM SQLPLUS, AN INVOCATION MIGHT LOOK LIKE THIS:
-- EXECUTE LOAD_MY_FILE('D:\DATA','MY_EXCEL.TXT')
v_file UTL_FILE.FILE_TYPE
:= utl_file.fopen(p_directory,
p_file_name,'R');
v_inventorynum NUMBER;
v_cost NUMBER;
v_supplier VARCHAR2(100);
v_string VARCHAR2(2000);
v_first_comma NUMBER;
v_second_comma NUMBER;
BEGIN
-- THE FOLLOWING LOOP LOOKS LIKE AN INFINITE LOOP.
-- HOWEVER, THIS IS JUST A QUICK AND DIRTY APPROACH TO
-- LOOPING THROUGH A FILE. WHEN WE HIT THE END OF THE
-- FILE, AN EXCEPTION WILL BE RAISED. AT THAT POINT,
-- WE'LL JUMP OUT OF THE LOOP AND GO TO THE EXCEPTION
-- HANDLER. IF YOU'RE WORRIED ABOUT TRAPPING SPECIFIC
-- EXCEPTIONS, YOU'LL HAVE TO REFINE THIS ROUTINE!
LOOP
-- GET THE RECORD
utl_file.get_line(v_file,v_string);
-- FIND THE DELIMITERS
v_first_comma := instr(v_string,',');
v_second_comma := instr(v_string,',',-1);
-- POPULATE THE VARIABLES
v_inventorynum := substr(v_string,1,v_first_comma-1);
v_cost := substr(v_string,v_first_comma + 1, v_second_comma - v_first_comma - 1);
v_supplier := substr(v_string, v_second_comma + 1);
-- NOW UPDATE THE RECORD
UPDATE my_table SET cost = v_cost, supplier = v_supplier WHERE (inventorynum = v_inventorynum);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
COMMIT;
utl_file.fclose(v_file);
END;
