2.11 Known Problems

These topics describe known problems with Oracle Database Gateway for SQL Server and includes suggestions for correcting them when possible.

A current list of problems is available online. Contact your local Oracle office for information about accessing the list.

• Encrypted Format Login
  The DBLINK_ENCRYPT_LOGIN initialization parameter is obsolete.
• Date Arithmetic
  SQL expressions that do not function correctly with the Oracle Database Gateway for SQL Server.
• SQL Server IMAGE, TEXT and NTEXT Data Types
  There are restrictions that apply when using the IMAGE, TEXT, and NTEXT data types.
• String Functions
  If you concatenate numeric literals using the "||" or CONCAT operator when using the gateway to query a SQL Server database, the result is an arithmetic addition.
• Schema Names and PL/SQL
  You must prefix a SQL Server database object with its schema name in a SQL statement within a PL/SQL block.
• Data Dictionary Views and PL/SQL
  You cannot refer to data dictionary views in SQL statements that are inside a PL/SQL block.
• Stored Procedures
  Return values of stored procedures that return result sets are incorrect.

2.11.1 Encrypted Format Login

The DBLINK_ENCRYPT_LOGIN initialization parameter is obsolete.

The Oracle9i database (Release 9.2 and earlier) supported an Oracle initialization parameter, DBLINK_ENCRYPT_LOGIN. When this parameter is set to TRUE, the password for the login user ID is not sent over the network.

If this parameter is set to TRUE in the initialization parameter file used by the Oracle9i database, you must change the setting to FALSE, the default setting, to allow Oracle9i to communicate with the gateway.

In the current release, the DBLINK_ENCRYPT_LOGIN initialization parameter is obsolete, so you do not need to check it.

2.11.2 Date Arithmetic

SQL expressions that do not function correctly with the Oracle Database Gateway for SQL Server.

Statements with the following SQL expressions are sent to the SQL Server database without any translation. Since SQL Server does not support these date arithmetic functions, the statements return an error.

date + number
number + date
date - number
date1 - date2

2.11.3 SQL Server IMAGE, TEXT and NTEXT Data Types

There are restrictions that apply when using the IMAGE, TEXT, and NTEXT data types.

For example:

• An unsupported SQL function cannot be used in a SQL statement that accesses a column defined as SQL Server data type IMAGE, TEXT, or NTEXT.
• You cannot use SQL*Plus to select data from a column defined as SQL Server data type IMAGE, TEXT, or NTEXT when the data is greater than 80 characters in length. Oracle recommends using Pro*C or Oracle Call Interface to access such data in a SQL Server database.
• IMAGE, TEXT, and NTEXT data cannot be read through pass-through queries.
• If a SQL statement is accessing a table including an IMAGE, TEXT, or NTEXT column, the statement will be sent to SQL Server as two separate statements. One statement to access the IMAGE, TEXT or NTEXT column, and a second statement for the other columns in the original statement.

The gateway does not support the PL/SQL function COLUMN_VALUE_LONG of the DBMS_SQL package.

See Also:

Supported SQL Syntax and Functions for more information about restrictions on SQL syntax.

2.11.4 String Functions

If you concatenate numeric literals using the "||" or CONCAT operator when using the gateway to query a SQL Server database, the result is an arithmetic addition.

For example, the result of the following statement is 18:

SQL> SELECT 9 || 9 FROM DUAL@MSQL;

The result is 99 when using Oracle to query an Oracle database.

2.11.5 Schema Names and PL/SQL

You must prefix a SQL Server database object with its schema name in a SQL statement within a PL/SQL block.

If you do not prefix a SQL Server database object with its schema name in a SQL statement within a PL/SQL block, the following error message occurs:

ORA-6550 PLS-201 Identifier table_name must be declared.

Change the SQL statement to include the schema name of the object.

2.11.6 Data Dictionary Views and PL/SQL

You cannot refer to data dictionary views in SQL statements that are inside a PL/SQL block.

2.11.7 Stored Procedures

Return values of stored procedures that return result sets are incorrect.