Re: Updating a table w/PRO*C

In article <ccahdm.6.2D5265B5_at_beluga.upe.ac.za>, ccahdm_at_beluga.upe.ac.za (Donald Munro) writes:
|> In article <CKCz8J.J22_at_amiserv.chi.il.us> genanr_at_amiserv.xnet.com (Andrew Ryan) writes:
|>
|> > Does anyone have any good ideas of how to do the following quickly?
 

|> >I have an ascii fixed file with approx. 4-5 million records
 

|> >I need a way of taking each record and if a key exists in the database
|> >update the fields in the table, otherwise insert the whole record.
 

|> >I would like to do this is PRO*C since I have had pretty good luck with it
|> >in other applications. I would also like to try doing it with host arrays.
|> >I have tried several methods, but it is either slow or doesn't work.
 

|> >My main problem seems to be if I try to do something like this:
 

|> >INSERT d:data where key=:key
 

|> >However, if it gets to a key that is already in the table, it jumps out of
|> >the insert statement rather than going on the the next element in the array.
 

|> >Thanks in advance,
 

|> > Andy
|>
|> If the key is a primary key or unique index then the following should work
|>
|>
|>
|> #define ORA0001 -1 /* ORA-00001 Unique constraint etc etc */
|> ..
|> ..
|> void update_table(void)
|> /*---------------------------------*/
|> { ..
|> ..
|> EXEC SQL WHENEVER SQLERROR GOTO ERRINSERT;
|> INSLOOP:
|> EXEC SQL
|> INSERT INTO TAB (.....) VALUES (....);
|> ..
|> ..
|> ..
|> goto INSLOOP;
|> ERRINSERT:
|> if (sqlca.sqlcode == ORA0001)
|> { UPDATE TAB
|> SET COL = ...
|> WHERE ....
|> }
|> else
|> { sqlerror(PROGRAM,"update_table()");
|> exit(-1);
|> }
|> goto INSLOOP;
|>
|> }
|> ..
|>
|> Regards,
|> Donald Munro (ccahdm_at_beluga.upe.ac.za)
|> Computing Centre University of Port Elizabeth
|>
|>

This solution does not work for host arrays, only for looping through the records one at a time. There is no solution in PRO*C for host arrays. However, you might consider using Sql*Loader. Have SQL*Loader load the table throwing the bad records into a bad file. The bad file can then be used by a PRO*C program that only does updates.

-- 
Derek Fields
(derek_at_gamekeeper.bellcore.com)