We’ve talked about the AUTO REST feature of ORDS for TABLES quite a few times here. What I want to talk about today is how you can take advantage of that TABLE REST API in your favorite Excel spreadsheets.
The Oracle Visual Builder Add-in for Excel ‘allows Excel users to edit business data available from REST services.’ Well, that’s pretty much what ORDS offers. You can find the docs here.
The installer is very simple. Open Excel and you’ll see a new item in your ribbon.
Hit the ‘Designer’ button, and you’ll get prompted for a URI that contains ‘open-api-catalog’, and I gave mine this
http://localhost:8080/ords/hr/open-api-catalog/beers/
ORDS OpenAPI links for REST enabled tables includes the docs for the GET, PUT, POST, and DELETE end points on your collection – which in this case, is my UNTAPPD table.
Once it gets started, it’ll ask for a username/password. I left mine blank, but I’ve also tested the ORDS user/password basic auth, and both work just fine.
You’ll get the list of columns back in your table.
Now comes the fun part – hit the ‘Download Data button’
It’ll fetch data until it gets to 499 rows, and then ask you if you want to GET the rest or not:
Now I have some data in my Excel sheet. Let’s change a value.
I changed a label for my beer, and marked the ‘Change’ column.
And…success!
This smells like witchcraft to me, let’s go check the database.
So what does this mean?
The VBCS team built a nice plug-in for Excel. If you have an ORDS REST Enabled TABLE, you can now also use Excel as a front end for managing your data. Now, I might rather you build an APEX app for that, but folks do seem to like Excel an awful lot…
2 Comments
please ignore the last note. i missed part of the URL 🙁
all good..
thanks again jeff.
Hi Jeff,
Thank you.
I download Visual Builder and Installed it.
I clicked on Designer and tried the web address (https://myserver.com/ords/mybasepath/myobjectalias/)(Note: the link works from my browser). I clicked ok.. ignored the authentication, simply clicked sign in. I get the following error
Error
Unable to load and analyze the service description from https://insumlabs.com/webapps/odg/odg_builtin_color/. Ensure that the service description is a valid OpenApi document. (VBAFE-01006)
Details
Unable to load and analyze the service description from https://insumlabs.com/webapps/odg/odg_builtin_color/. Ensure that the service description is a valid OpenApi document. (VBAFE-01006)
OpenAPI specification version ” is not supported.
~~~~~~~~~~
UnableToLoadServiceDescriptionException: Unable to load and analyze the service description from https://…… Ensure that the service description is a valid OpenApi document. (VBAFE-01006)
Stack:
Inner:
OpenApiUnsupportedSpecVersionException: OpenAPI specification version ” is not supported.
Source: Microsoft.OpenApi.Readers
Stack:
at Microsoft.OpenApi.Readers.ParsingContext.Parse(YamlDocument yamlDocument)
at Microsoft.OpenApi.Readers.OpenApiYamlDocumentReader.Read(YamlDocument input, OpenApiDiagnostic& diagnostic)
at Microsoft.OpenApi.Readers.OpenApiTextReaderReader.Read(TextReader input, OpenApiDiagnostic& diagnostic)
at Microsoft.OpenApi.Readers.OpenApiStringReader.Read(String input, OpenApiDiagnostic& diagnostic)
at Oracle.VisualBuilder.ServiceManager.OpenApiHelper.ParsePayload(String payload)
at Oracle.VisualBuilder.ServiceManager.OpenApiHelper.CreateFromPayload(String payload)
at Oracle.VisualBuilder.Excel.Runtime.Designer.LayoutSetupHelper.TryGetService(String context, Boolean showServices, ServiceDefinition& serviceDef)
I researched and found an Oracle note Doc ID 2685987.1, but cannot successfully call fscmRestApi ( I tried it in the URL, browser and Excel). Can you please help.
Thanks ,
Krishna