Main parameters to setup a Transparent Gateway connection to SQL Server


I would like to share with you the parameters that worked out for me when connecting to a SQL Server database.

Please consider that I am not explaining in detail how to set up a transparent gateway connection to SQL Server.

I was asked to setup a connection from Oracle to SQL Server 2005 using transparent gateway, When I was checking up the SQL Server there was already a transparent gateway installed on it (10.1) but it was not configured yet. so I decided to work with that one and then I downloaded thet same version and installed it on a development server.

After starting it I performed some basic tests and then pass on the testing environment to the developers.

Later on the developers reported the following errors when uploading a CSV file from APEX to one table on SQLServer.

ORA-02054: transaction 18.154.59645 in-doubt
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
trying all tdps, ignoring failure
ORA-02063: preceding 2 lines from SQLDEV_WEB_USER
ORA-06512: at line 6


ORA-28551: pass-through SQL: SQL parse error
[Transparent gateway for MSSQL][Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite (send()).[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation. (SQL State: 00000; SQL Code: 11)
ORA-06512: at "DEVE.SP_INS_LEAD", line 20
ORA-06512: at "DEV.INS_SQL", line 7
ORA-06512: at line 3

After cleaning some pending transactions (see how here) I realized that the default configuration was not enough and then I did some research on this, after that I came up with what I consider the main parameters to set up to connect to SQL Server.

Oracle Transparent gateway reads the parameters from the following file:


in my case:

These are the parameters that best fitted my situation and I am going to explain them one by one:


As you can see these are Heterogeneous Services parameters and are different from those we are used to see on Oracle database servers.

Now lets review them one by one:

This is the name of the target database instance: servername.dbname

This is a very important parameter, specially for performance, in simple words, when this parameter is set to YES (the default value) the agent will not send data to the Oracle system until it has fulfill the size specified in the HS_RPC_FETCH_SIZE parameter, when we set this parameter to OFF it will send the data immediately it is ready. This parameter can be OFF only when the non-Oracle system supports array fetches.

This parameter enables Read/Write transactions on the remote database, in order for it to work it needs a transaction table, specified in the next 3 parameters. Other values for this parameter are: READ_ONLY and SINGLE_SITE

This parameter specify the name of the transaction log table, we have to grant it SELECT, DELETE, and INSERT privileges to public.

We have to provide an SQLServer account to work with the HS_FDS_TRANSACTION_MODEL commit_confirm model, that will help us overcome the pending transactions, I used the same account set up in the DB LINK from Oracle.

The password for this account.

This parameter is not really necessary to avoid pending or in-doubt transactions, but the developers were using PL/SQLs with dinamic SQL and I had to increase this value on the agent, the maximun value is the value for the OPEN_CURSORS parameter on the Oracle database and a default value of 50.

By default, all stored procedures and functions do not return a return value to the user. To enable return values we need to set it to TRUE. This parameter does not work if you have

Note: If you set this gateway initialization parameter, you must change the syntax of the procedure execute statement for all existing stored procedures, visit the official documentation below

The same as before, but returns a whole RESULTSET instead of a value. This parameter will leave ineffective the previous one.

Note: If you set this gateway initialization parameter, you must change the syntax of the procedure execute statement for all existing stored procedures, visit the official documentation below

Here is the official documentation for the Transparent Gateway regarding MS SQL Server

This entry was posted in Oracle Server, Performance & Tuning and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s