We need to generate a report which combines information from 7 different external oracle databases.
Once the information is retrieved from the external databases it needs to be joined with data from our internal database.
Currently we are using database links in the internal database to retrieve the data from the external databases.
The data is returned in table types which are then cast and used in a select statement linking it to tables in the internal database.
Once the data from all the databases have been retrieved, linked and limited a REF CURSOR is returned to a .net program which displays the information for the user.
One of the developers on the team has suggested that instead of using database links we should use .net web service calls inside the internal database to retrieve the external data.
I'm not sure if this is the best approach. To the best of my knowledge .net cannot accept/return table types only REF CURSORS - which means that table types could not be used to return the data from the external databases.
We want to keep all of the database processing in one place as close to the data as possible, using pl/sql and packages
Using .net web service inside a database is a new concept to me as I have always used database links. I do not want to dismiss the suggestion without looking at the pros and cons. I would appreciate any comments, insites, suggestions, etc. that you may have.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.