SQL statement that updates an Oracle database table from an Excel table

Question

I would like to write a SQL Update command that can be run once a year to update a record for each account in an Oracle database based on external values ​​obtained in an Excel spreadsheet.

My research so far shows that I can use the OPENROWSET command, but most of the links show that this is used in Excel for MS SQL Server:

INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\foldername\spreadsheetname.xls;', 'SELECT column1name, column2name, column3name, column4name FROM [worksheetname$]') EXT 

Can anyone verify that I'm on the right track or is it even better to provide a basic example?

The main logic of Psuedo is as follows: For each record in the USER_DEFINED Oracle table where CODE_FIELD is "CRS" And where I have a value in the spreadsheet with the corresponding account number, update the VALUE field for this record in the Oracle table USER_DEFINED with the content "Value "in the table.

Answer 1

Not what you request, but if I were you (and since then just once a year), I would create update statements in Excel using concatenation formulas.

If the first rows / columns of Excel look like this:

 ACCT_NBR | NEW_VALUE | CONSTRUCTED_SQL_STMT 123 | Hello | ="Update USER_DEFINED Set VALUE = '"&B2&"' Where CODE_FIELD='CRS' And Account_Num='"&A2&"';" 456 | World | ="Update USER_DEFINED Set VALUE = '"&B3&"' Where CODE_FIELD='CRS' And Account_Num='"&A3&"';" 

Then simply run a copy / paste of the resulting series of update statements in SQL * Plus. Any that do not have a match in your database will not initiate an update, and anything that matches will be updated.

Make a commit at the end and you're done!