>>I have a system that pulls from two datasources: a SQL 6.5 database and an ORACLE database. (I do not code or maintain either database. They are client databases.) I'm able to access a stored procedure on the SQL database and get the recordset with no problems. The problem is the ORACLE database. They want to write the stored procedure and return the recordset, but we can't get it to work. Is there a way to do it using SQLEXEC() as I do with the SQL database? All the examples I've seen creates the stored procedure from FoxPro and then accesses it. They don't want me to create the procedure from my end. I don't think the problem really is my FoxPro code. They have admitted to me that they have never done this before and aren't sure how to pass a result set.
>>
>>Can anyone help for the code on BOTH ends?
>>
>>TIA
>>
>
>You have to have a separate connection in the DBC for the the SQL Server DB and the Oracle DB. See article #233 in the KB under Articles, Client/Server on connecting VFP to Oracle. Using this connection, you can use SPT [SQLExec()] and/or remote views to access Oracle data.
>
>As for creating stored procedures and whatever else they prefer to have on the Oracle side, I recommend looking for a good technical reference book on Oracle that covers PL/SQL. This can be done fairly easily by writing SQL script files then executing them from SQL*Plus. Using script files will ensure you can duplicate what you created in case something goes wrong in future.

My problem hasn't been connecting to the database, it's been pulling the result set. I have successfully called other stored procedures on the ORACLE database that didn't return result sets.

This is also a situation where users will be going through a Multi-threaded COM DLL running on MTS. It's a situation where I'm going to have a record locator number and passing that to the stored procedure on ORACLE. I then will get a result set which will be inserted into my Database system. It's easy to do with SQL Server, but ORACLE is being a pain.

Thanks for the input.

Tom