Re: Need assistance with Oracle 9i calling stored procedure on SQL Server via Gateway

"Steve" <sdrichardson_at_direcway.com> wrote in message news:be02422b.0304091213.23b158b5_at_posting.google.com...
> I'm in serious need of direction & assistance on the use of
> Transparent gateway from Oracle 9i to a stored procedure on SQL
> Server. This is taking place in a web environment where the actual
> execution is taking place on the web server, not a client machine.
>
> In the old Forms 6 environment under client / server (not web), this
> was accomplished via ODBC on the client machine using the EXEC_SQL
> facilities to define a connection, cursor, etc., to call the
> procedures, define columns, return the result set via
> EXEC_SQL.EXECUTE_AND_FETCH, etc.
>
> In the Forms 9i environment, this is no longer a viable approach.
> Oracle says we need to make use of transparent gateway, which we have,
> to accomplish this same task. It should be noted that the stored
> procedure on the SQL Server side returns a result set, not a simple
> single value.
>
> If someone has just a handful of lines of code I can use as reference
> code as to how to go about doing this via the gateway, it would be
> most appreciated. Responses may be posted here or simply Emailed
> directly to sdrichardson_at_direcway.com
>
> Thanks in advance for any and all assistance.
>

Steve,

Just a thought (and sorry if this doesn't help).

How up-to-date does the query need to be? If not up-to-the-second, have you considered using a simple SQLServer DTS job to replicate the data into Oracle every few minutes? Then the Oracle query could stay in its own sandbox, and no doubt perform much better, but of course the downside is (potentially) stale data from the SQLServer side. Is this real-time, or what?

Tell more, and I'll try to help more.

Regards,
Paul Received on Wed Apr 09 2003 - 20:06:35 CDT