PARADOX <-> SQL LINK <-> ORACLE

I need some advice on PARADOX <-> SQL LINK <-> ORACLE.

All my experience has been as a DBA on the ORACLE side and I have 2 hours exposure to PARADOX and SQL LINK.

We are supporting the development of a PARADOX application with a local PARADOX database and a non-local ORACLE database.

They want to do a join on a PARADOX table with an ORACLE table and populate another PARADOX table with the results.

The pseudo code looks something like:

    Open PARADOX table
    read PARADOX record
    Loop:

      execute SQL script to retrieve ORACLE data
          based on keys taken from current PARADOX record
      insert new PARADOX record
      read PARADOX record 

What appears to be happening is that the SQL script is being passed through to ORACLE for EVERY PARADOX record and incurring the parsing overheads EVERY time.

Q1. Is that what is happening OR is PARADOX(SQL LINK) smart enough to

     open an ORACLE cursor and repeatedly use it with different key values?

Q2. Is there any way to use the ARRAY capabilities of ORACLE from PARADOX

     to reduce the number of times I have to execute SQL scripts?
     ie some way of saying (in the SQL) 'get me all rows that match the 
     values in field X of this paradox table'.

One solution I have is to dynamicly create the SQL statement and simply have a large WHERE clause that picks up each ORACLE record using a lot of 'OR' statements. This will reduce the number of executes (and parsing?) but at the expense of larger scripts. (I have a feeling that THIS is the only workable solution)

The other solution would be to load all the PARADOX keys up into a temporary ORACLE table, and then do the select as a join of the two ORACLE tables and merge the results with the local PARADOX table and ... this seems to be counter-productive to me.

Q3. The SQL LINK manual mentions that you can use stored procedures and

     triggers but doesn't give any details.  How do you access packaged 
     procedures and PL/SQL and get results back?

Any help appreciated.                                   

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************


* Bruce Pihlamae  --  Database Administration                     *

* Commonwealth Department of Human Services and Health            *


* Canberra, ACT, Australia                        (W) 06-289-7056 *


*=================================================================*


* These are my own thoughts and opinions, few that I have.        *


*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras