I am receiving files which contains more than 500 vendors information. depending on the vender name we need to get some additional data from oracle table. If we put a loop on the vender name and perform select adapter service, then for each vendor webMethods will connect to database. Its like connecting to database more than 500 times. This is not a best practice, Because of performance and connectivity issues.

I need a solution which takes all the vendor names and select the data from database in a single transaction. webMethods should get all the information in a single transaction.

Please provide me valuble solutions, Thanks.

Connection pooling, when enabled, will address avoiding making a connection for each query. The loop will use 1 connection for all 500 selects. Depending upon the connection configuration, and the optional use of explicit transaction management, the selects can all be in one DB transaction or auto-committed.

If you want to be able to select multiple records in one select call, you’ll need to figure out the SQL query that will get them.

Refer to the JDBC Adapter User’s Guide for additional information.

Reamon, Thanks for your reply. Is it possible to write a stored procedure which takes the string list an input and generates the output data as a document list.
The following statements are contradict with each other. I found these 2 statements in JDBC developer guide.
1:- Stored procedures provide greater flexibility in performing database operations in response to documents.
2:-[FONT=Arial][SIZE=3][FONT=Arial][SIZE=3]Note: [/SIZE][/FONT][/SIZE][/FONT]StoredProcedure services do not support stored procedures that have Array or Struct as OUT parameters.

I am confused with the above statements. Please provide me your suggestions.

I am assuming that all the vendor details are maintained in one table.

1. Get all the records from the table.
2. Load all the records into cache(create Java service).
3. Get the vendor details from the cache instead of getting the records from the table for each time.
4. Incase of if any new records are inserted into table then repeat the 1 & 2 steps.
   Note: Incase of if any delta changes for records, better to do as per Reamon suggestions.

Hi jeevan,

The table has more than 15000 records. So, This is not good to get all records in to cahce. Here my question is…
can we use a stored procedure to solve my issue?

A stored proc could do the trick. The JDBC adapter supports Oracle ref cursors. Have the proc return a ref cursor and the adapter will read all the data from the cursor before returning–giving you a list of the records.

1. one of previous project ,the table has more than 40,000 records , retrieved the records from the cache instead of getting the records from the table .
2. I do not have an idea about stored procedure. Can you try it as per Reamon suggestion.