From Excel Straight Into Oracle Tables
Business users want to manipulate data in Microsoft Excel, and IT professionals want data securely stored in the Oracle database. The IT department has tried to provide an Excel-like user experience with various tools. From ADF 12c and APEX 18 onwards, it was possible to build a passable spreadsheet-like experience and some "Export to Excel" features. But what users really wanted was to work in Excel with data in the database. Now they can.
We saw a precursor of this functionality in what is known as Oracle ADF Desktop Integration, but that functionality required the developer to build an Oracle ADF data control in JDeveloper and deploy to an application server. ADF is a powerful tool, but few Oracle developers picked it up, and ADF Desktop Integration languished in obscurity in a few organizations.
With Visual Builder Cloud Service, Oracle is again trying to address the needs of developers and power users for simple applications. The Visual Builder Add-in for Excel allows an end user to work inside Excel with data in a VBCS business object. But it can also work with data published with the Auto REST feature of Oracle REST Data Services (ORDS).
Since the VBCS Addin is free to download (even if you don't have a VBCS license) and ORDS is part of your database license, you now have all the tools you need included in your database license.
To allow your users to work directly with tables, you must:
Set up Auto REST services for the table you want your users to work directly with
Install the VBCS Add-in for Excel on the users' workstations. It will appear with its own tab in the icon ribbon inside Excel
Give the user the URL of the data API from ORDS Auto REST and a valid username/password
Now the user can click Download Data and get data straight from the table into Excel. They can change it and click Upload Changes to automagically commit the update to the database. I'm describing this in the context of VBCS in my upcoming VBCS book and Jeff Smith has described the process in the context of ORDS on his blog.
Oracle is working hard to convince customers to keep the Oracle database as a central part of their IT platform. For most Oracle customers it makes sense to keep existing Oracle databases. But do consider untangling yourself from other Oracle products.
Best regards
Sten Vesterli
sten@vesterli.com