Updateing Table Info from Delimited File 2

[bpitche]

As we have just upgraded our database to Oracle, it is taking a lot to learn everything. What we have been doing is pulling price data on inventory and sending it to our purchasing department to send out for bids (in excel). They are making changes to prices and sending it back to us (also in excel). I am taking that excel file and saving it in a delimited format, but I don't know how to take that file and update just these fields in the Oracle table. The format of the the file would be (inventorynum, cost, supplier). I am not sure if I should be using SQL*Loader or what. If someone follows what I am asking, I would appreciate it if you could help me out. X-)

Thank you,
Brad Pitcher
The Hoover Company Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[carp]

Brad -
If you were just INSERTing the data, you could use SQL*Loader. However, since you are UPDATEing, I would try something like the a stored procedure that does the following:

- Using utl_file, open the delimited file.
- For each record in the file
-- Get the inventorynum, cost, supplier
-- Update the appropriate row
- COMMIT
- Close the file

 

[bpitche]

Carp-
Thank you for the reply, but could you please go into a little bit more detail. I am not sure what the utl_file is. An I am not sure how to write the update procedure for the file.

Thanks again, Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[lfoata]

SQL loader is the best way to LOAD data from delimited files to the database.
It is very efficient (look at the tuning parameters). Much more efficient than using sql plus or an ODBC.
Keep in mind, SQL loader is meant to load data (rows), not to do updates on existing rows.

It is also very easy to use.

 

[carp]

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;

 

[bpitche]

Carp-
Thank you very much for taking the time to do that. I will see what I can make of it. I appreciate it!!
Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[bpitche]

Carp -

I found an easier way of updating data in a table from a comma delimited file. Your comma delimited file should look like this:
(the :#'s represent the column in the comma delimited data)

update inventory
set lastcost = :2, vendor = :3
where itemnum = :1
IP1116437,6.76,3425070
IP1117775,10.77,3425070
IP1150678,4.45,3425070
IP1152140,4.82,3425070
IP1175420,131.28,3425070
/

Hopefully this will save you some time, next time. Anyways, just thought that I would share that with you seeing that you spent so much time trying to help me.

Thanks, Brad Pitcher
The Hoover Company
bpitcher@hoover.com

 

[carp]

VERY nice! Thanks!