Thanks for the question, Evan.
Asked: February 24, 2017 - 10:59 pm UTC
Last updated: February 25, 2017 - 2:13 am UTC
Version: 11
Viewed 1000+ times
You Asked
Hi there,
There are two applications, which I'll call Application A and Application P. Both applications are third party products with limited ability to modify the application. However, it's possible to make changes to the database, such as adding procedures and functions, creating tables, etc. The databases are Oracle 11.
There is a requirement for Application A to query some data in Application P. What I'm thinking of doing is:
1. In Application P, create a new schema. Let's call it external_api.
2. In external_api, create a function (let's call it get_data) that will have one or more parameters for the data from Application A and the return value will be a nested table collection for the data from Application P (the nested table type will be defined in external_api). The get_data function will use the parameter passed in (from Application A) to retrieve the data and build a nested table collection to return. get_data will query tables in other schemas in the same database to get the data to populate the collection. (If the data passed in is invalid, an exception will be raised, probably no_data_found, although I may define a new exception type in external_api.)
3. In Application P, create a new user. Let's call it external_api_user.
4. Grant execute on the get_data function to external_api_user.
5. Create a database link from Application A to Application P that connects using external_api_user.
6. In Application A, write a procedure that calls the get_data function in Application P.
With this approach, Application P will appear as "black box" to Application A. In other words, Application A does not have to concern itself with the internal details of Application P, such as table structure, column names, etc. If the vendor changes the tables and columns in Application P, all that's required is to change the queries inside the get_data function that retrieve the data and there's no impact to Application A.
Given the constraint that I can really only do the integration at the database level, is this the best approach? Or is there another approach that is better? More generally, what are the best practices for integration between applications at the database level?
Thanks in advance.
and Connor said...
Basically the aim here is what is known as Loose Coupling
https://en.wikipedia.org/wiki/Loose_coupling where you want to establish a reasonable level of isolation between applications (and components within those applications).
I dont see any issues with your proposal, except to add that I would not "over-engineer" things until the need arises. So
a) the separate schema, API based approach - looks good to me. The API provides encapsulation, the separate schema provides a good "least needed privilege" capacity
b) database link ... I wouldnt bother. If there are in the same database, then just use synonyms/grants as appropriate whilst still preserving (a) above. If one of the apps *does* move to a new database, *then* look at using db links.
c) nested tables. Dont limit yourself to just this - perhaps ref cursors in some cases will be better, perhaps scalar results in other cases. I wouldnt impose limits unless absolutely necessary.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment