Here is my thoughts/idea

1. You like to create a stored procedure.

I have in the long past played with Oracle Call Interface (OCI) a lot. The are a variety of wrappers for it, that make OCI very friendly to use. I would write a C/C++ user defined function(UDF) and call it in the Stored procedure.
This would work on both Windows and Unix/Linux. It would be fast, quick, OCI offers good error catching as well.
I would create one UDF to handle the connection to oracle, per firebird connection, then create further libraries that perform the differing queries to Oracle.

You could of course simply write user defined functions in Pascal or Python as well that use some connection to oracle. Oracle on Windows has good Java and Delphi interfaces.

I believe that any resulting connection in a UDF would be complex, an error in it may cause FB to have issues if it fails, that may take the FB database down. Hence the thought of sub libraries for each differing query, so that different parts of the library can be changed more easily.

2. Alternatives

Import the data into FB? or add to Oracle a bunch of triggers that call code that updates FB.


I know of no native connection between oracle and FB.

hth


alex