Oracle Stored procedure call in Java. Required design suggestions

Question

I'm looking for design suggestions on a specific requirements for a customer. The requirement is to fetch the required data from an external Data Ware house system and generate reports based on it.

My System is build in Java and the external DW system cannot provide any Web service for us and hence we came up with an idea and asked for Stored procedure from DW system. Here is how I thought of this design.

Fetch the required data from my system and pass the information to DW Stored procedure. For e.g pick all the customer created in between certain dates and pass on the array of customer_no ( Unique and same in both the systems).

DW SP will return chunk of data e.g Customer NO , Customer Name , Name of Equipment, Cost e.t.c.

Build and XML data from what ever DW SP has returned and generate a report out of it.

Now my concerns are :

Is there a better way to do this ? are there any challenges or limitations since the volume of the data input and output are unknown?

DW SP (StoredProcedure) is taking values in an Array , is there limitation on the volume of data passed to SP in an array ? Is there any performance concerns?

Since DW SP is returning the data from multiple columns in different array it will be difficult to map each record since there is a possibility that one of the column data is empty in that case empty may not be inserted and the row data fetched will not be consistent, to resolve this is there a better way of doing it ? for e.g table ?

Appreciate your thoughts and help on this.

Upvotes: 1

Views: 138

Answer 1

If the data ware house system is also using an Oracle database you can link it to your Java system database and query it with simple SQL queries this will make your architecture easier.

Check oracle database links