Saturday, December 11, 2004

Stored Procedures for Oracle through Dot Net

One of the requirements in the current Project is to call queries from Oracle and perform validations. We are calling about 100 of the queries using "script funtoids" in the mapper. I wanted to optimize the database connections. Instead of sending 100 queries and hitting the database 100 times. I wanted to have single stored procedure to replace the 100 queries. Having worked with stored procedures for SQL database before, I did not find it an issue. My plan was to create an array that is input to the Stored Procedure and get an array as an output to the stored procedure. The idea was to send all possible variables in an array to the stored procedure. Process them and get possible values as an array. Use this array to populate the "Right Hand" side of the map.

On course of my effort, I realized that there are limitations as such of working with Oracle through arrays. I gave up after trying!