Oracle® Database Gateway for SQL Server User's Guide, 11g Release 2 (11.2) Part Number E12069-01 |
|
|
PDF · Mobi · ePub |
This appendix contains the following sections:
With a few exceptions, the gateway provides full support for Oracle DELETE
, INSERT
, SELECT
, and UPDATE
statements.
The gateway does not support Oracle data definition language (DDL) statements. No form of the Oracle ALTER
, CREATE
, DROP
, GRANT
, or TRUNCATE
statements can be used. Instead, use the pass-through feature of the gateway if you need to use DDL statements against the SQL Server database.
Note:
TRUNCATE
cannot be used in a pass-through statement.See Also:
Oracle Database Reference for detailed descriptions of keywords, parameters, and options.The DELETE
statement is fully supported. However, only Oracle functions supported by SQL Server can be used.
See Also:
"Functions Supported by SQL Server" for a list of supported functions.The INSERT
statement is fully supported. However, only Oracle functions supported by SQL Server can be used.
See Also:
"Functions Supported by SQL Server" for a list of supported functions.The SELECT
statement is fully supported, with these exceptions:
CONNECT BY
condition
NOWAIT
START WITH
condition
WHERE CURRENT
OF
The UPDATE
statement is fully supported. However, only Oracle functions supported by SQL Server can be used.
See Also:
"Functions Supported by SQL Server" for a list of supported functions.All functions are evaluated by the SQL Server database after the gateway has converted them to SQL Server SQL equivalents. The exception is the TO_DATE
function, which is evaluated by the gateway.
Oracle SQL functions with no equivalent function in SQL Server are not supported in DELETE
, INSERT
, or UPDATE
statements, but are evaluated by the Oracle database if the statement is a SELECT
statement. That is, the Oracle database performs post-processing of SELECT
statements sent to the gateway.
If an unsupported function is used in a DELETE
, INSERT
, or UPDATE
, statement, the following Oracle error occurs:
ORA-02070: database db_link_name does not support function in this context
The gateway translates the following Oracle database functions in SQL statements to their equivalent SQL Server functions:
Oracle | SQL Server |
---|---|
= |
= |
> |
> |
< |
< |
>= |
>= |
<= |
<= |
<>, !=, ^= |
<> |
IS NOT NULL |
IS NOT NULL |
IS NULL |
IS NULL |
Oracle | SQL Server |
---|---|
||, CONCAT |
+ ( expression1 + expression2 ) |
ASCII |
ASCII |
CHR |
CHAR |
INSTR (with two arguments) |
CHARINDEX |
LENGTH |
DATALENGTH |
LOWER |
LOWER |
LTRIM |
LTRIM |
RTRIM |
RTRIM |
SUBSTR (second argument cannot be a negative number) |
SUBSTRING |
UPPER |
UPPER |
Oracle | SQL Server |
---|---|
ABS |
ABS |
CEIL |
CEILING |
COS |
COS |
EXP |
EXP |
FLOOR |
FLOOR |
LN |
LOG |
LOG |
LOG10 |
MOD |
% |
NOT NVL |
IS NOT NULL |
NVL |
IS NULL |
POWER |
POWER |
ROUND |
ROUND |
SIN |
SIN |
SQRT |
SQRT |
TAN |
TAN |
If an Oracle function has no equivalent function in SQL Server, the Oracle function is not translated into the SQL statement and must be post-processed if the SQL statement is a SELECT
.
The gateway, however, does support the TO_DATE
function equivalent in SQL Server, as follows:
TO_DATE(date_string | date_column)
Where:
date_string is converted to a string with the following format:
yyyy-mm-dd hh:mi:ss.fff
Recommendation:
Supply the date string with the same format as the result (that is,y
yyyy
-
mm
-
dd
hh
:
mi
:
ss
.
fff
).date_column
is a column with a date data type. It is converted to a parameter with a timestamp data type.